Skip to main content
Solved

I need to sum up a duration from a table with m entries and refer the KPI to a dimension from another table with n entries (for more information see details or picture).

  • July 13, 2023
  • 5 replies
  • 31 views
jan.tran
Level 7
Forum|alt.badge.img+2

I have 3 tables:

  1. Fact Table A with n entries per case and a time dimension (round_hour)
  2. Case Table which containts the case keys
  3. Another Fact Table B with m entries per case and a time dimension (round_hour)

The data model is n : 1 : m. Unfortunately a generel snowflake model is not possible.

 

Now I need to sum up the duration from Fact Table B aggregated by the time dimension from eventtime2 (round_hour) and display the sum in a chart.

The time dimension for the chart is represented by the time dimension eventtime 1 from Fact Table A (round_hour).

 

I tried bind, domain_table in combination with PU-Functions but nothing worked.

 

Is there a any way map those time dimensions from different fact tables with a n : 1 : m relation?

 

mapping_time_dimensions_from_2_different_tables

Best answer by jan.tran

It worked with the new _CASEKEY so that no bind or domain table is needed.

--> CK1 | YYYY-MM-DD 06:00:00

--> CK1 | YYYY-MM-DD 07:00:00

--> ...

--> CK2 | YYYY-MM-DD 06:00:00

--> CK2 | YYYY-MM-DD 07:00:00

--> ...

mapping_time_dimensions_from_2_different_tables_new_casekey 

For the hourly sum of duration in a chart you can use the following PQL:

 

Please be aware of necessary filter_to_null if you have some component filter there because of the pu-function.

 

first(

  pu_sum(

    "_CEL_CASES"

    ,"_ACTIVITIY_TABLE_2"."DURATION"

    ,[some sort of filter from _ACTIVITIY_TABLE_2 if you need to]

  )

)

5 replies

jan.tran
Level 7
Forum|alt.badge.img+2
  • jan.tran
  • Author
  • Level 7
  • July 13, 2023

@Guillermo Gost 

Can you please extend your example?

Many thanks in advance!

G
Level 12

Oopps... I started to answer but then I get some doubst... besides I am on vacation :D

 

Anyway...

 

At first sight seems to be a typical case of N:1:N that is covered in the Build Analysis - Advanced course. Basically: PU to add the fact2 table results as a virtual column in the case table, then do the calculation....

 

The problem seems to be that, after that, you need to group per case and per hour.... then is when some doubts come to me.

 

I planned to try it first with a simple PU (my guess you already tried that...) Then a PU using a DOMAIN_TABLE with case_key and round_hour... at the last trick could be working somehow with BIND...

 

but, you know, everything a bit theoretical

 

BTW... what do you want to do? it seems like 2 activity tables... maybe will be easier with multieventlog?

jan.tran
Level 7
Forum|alt.badge.img+2
  • jan.tran
  • Author
  • Level 7
  • July 14, 2023

Hi Guillermo,

no problem, thank you for your answer - I appreciate it a lot! :)

 

Yes, I've already tried it but the results were not realistic.

 

Since you can pull an aggregation result to the CASETABLE I can redifine the _CASEKEY by adding the round_hour() to it

--> CK1 | YYYY-MM-DD 06:00:00

--> CK1 | YYYY-MM-DD 07:00:00

and so on.

 

Maybe it is possible to this workaround so that you dont need domain table and/or bind.

 

These tables are not 2 different activity tables. That's the reason multieventlog does not fit into my analysis.

I have to merge 2 types of table information into one chart.

 

I will update you asap.

 

G
Level 12

Got it. And yes, please share if you find a way. Best luck!

jan.tran
Level 7
Forum|alt.badge.img+2
  • jan.tran
  • Author
  • Level 7
  • Answer
  • July 17, 2023

It worked with the new _CASEKEY so that no bind or domain table is needed.

--> CK1 | YYYY-MM-DD 06:00:00

--> CK1 | YYYY-MM-DD 07:00:00

--> ...

--> CK2 | YYYY-MM-DD 06:00:00

--> CK2 | YYYY-MM-DD 07:00:00

--> ...

mapping_time_dimensions_from_2_different_tables_new_casekey 

For the hourly sum of duration in a chart you can use the following PQL:

 

Please be aware of necessary filter_to_null if you have some component filter there because of the pu-function.

 

first(

  pu_sum(

    "_CEL_CASES"

    ,"_ACTIVITIY_TABLE_2"."DURATION"

    ,[some sort of filter from _ACTIVITIY_TABLE_2 if you need to]

  )

)

Community Code of ConductAccessibility statement