5 Replies Latest reply: Jun 21, 2018 9:13 AM by Gysbert Wassenaar RSS

    Complex Canonical date case

    Carlos Alonso

      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 ;-).

      Qlik example model.PNG

        • Re: Complex Canonical date case
          Gysbert Wassenaar

          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;

            • Re: Complex Canonical date case
              Carlos Alonso

              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.

            • Re: Complex Canonical date case
              Rangam Seshadri

              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.