Complex Canonical date case

[Anonymous]

Hi,

I have a bit of a complex case of 'canonical date'. Complex in the sense that the dates are stored in different event tables instead of for example in 1 order details table as is used in the example in this forum.

I'd like to create a canonical date table so I can select all events from table TypeA AND table TypeB for any given date.

I've tried to create a bridge table, but then I seem to lose the right relation to the Orders table.

Some background information:

• any single order can have 0 or 1TypeA event on any given date
• any single order can have 0 or many TypeB events on any given date

I hope someone can help.

Best regards.

Carlos

p.s. I'm not that new to Qlik Sense but I had to pick a 'Place' to tie this thread to and wasn't able to select a more generic Place instead of 'New to Qlik Sense'. Any tips to move this thread to a better 'Place' are welcome ;-).

[Gysbert_Wassenaar · Accepted solution]

You could create an OrderEvents table

OrderEvents:

LOAD

     Order_ID,

     TypeA_EventID as Event_ID,

     'A' as EventType   

RESIDENT

     Orders

     ;

CONCATENATE (OrderEvents)

OrderEvents:

LOAD

     Order_ID,

     TypeB_EventID as Event_ID,

     'B' as EventType   

RESIDENT

     Orders

     ;

DROP FIELDS TypeA_EventID, TypeB_EventID FROM Orders;

tmpEventDates:

LOAD

     TypeA_EventID as Event_ID,

     'A' as EventType   

RESIDENT

     TypeA

     ;

CONCATENATE (tmpEventDates)

LOAD

     TypeB_EventID as Event_ID,

     'B' as EventType   

RESIDENT

     TypeB

     ;

LEFT JOIN (OrderEvents)

LOAD * RESIDENT tmpEventDates;

DROP TABLEs tmpEventDates,TypeA, TypeB;

[Anonymous]

Gysbert, thanks for your quick reply.

The OrderEvents table does not contain a date field. I don't understand how I can select events from both former Event tables from one selection.

Other question: does it matter if events A en B have totally different attributes? I guess not, but just checking.

If it's possible, I'd like to have a model where I can keep the EventA and EventB tables and your OrderEvents table just acts as a bridge table.This way I can keep all the original field names from the event tables instead of having to create generic names and using the Eventtype with set analysis for everything.

[rangam_s]

you can concatenate all other two tables under Orders, so that it will map accordingly. 1 to many or 0 based on the values present in other type table.

[swuehl]

I guess Gysbert just forgot to add the date info:

...

tmpEventDates:

LOAD

     TypeA_EventID as Event_ID,

     'A' as EventType ,

   TypeA_Date_ID as Canon_Date_ID

RESIDENT

     TypeA

     ;

CONCATENATE (tmpEventDates)

LOAD

     TypeB_EventID as Event_ID,

     'B' as EventType ,

    TypeB_Date_ID as Canon_Date_ID

RESIDENT

     TypeB

     ;

[Gysbert_Wassenaar]

Indeed. Good catch!