2 Replies Latest reply: Aug 7, 2015 5:56 PM by Anlee Cox RSS

    Preceding Load

    Anlee Cox

      I'm somewhat of a Qlik novice and need advice on how to organize my script loads. I'm hoping I can use preceding load, but I can't seem to get it to quite right.  I'm still transitioning from SQL logic into Qlik logic.  Any help would be appreciated!

      Example.PNG

      Group A and Group B are in daily QVD files. Psedu-code:

      Mobile:

           Load * where Device = Mobile;

      Desktop:

           Load * where Device = Desktop;

      From i to 30

           Load * from Group A

           Load * from Group B

      Next i;

       

      Left Join (Mobile) Load * from DeviceMobile;

      Left Join (Desktop) Load * from DeviceDesktop;

       

      AllData:

           Somehow concatenate Mobile and Desktop;

       

      Load *, Cost*ItemCount as TotalCost Resident(AllData);

        • Re: Preceding Load
          Borja Rodriguez

          This is how I'd dot it (one of the ways I can think of):

           

          I considered that the IDs of Mobile Devices and Desktop Devices are unique, therefore you can create a Mapping table containing all the Devices (both mobile and desktop) and apply the mapping onto the final table. After that, you can get the corresponding Cost from the Mapping table by using the ApplyMap function in the final table where you have the Device column.

           

          1. Load all the Device data into the same Mapping table:

          MAP_Devices:

          Mapping

          LOAD

               "Device Mobile" as Device,

               Cost

          FROM DevicesMobileTable;

           

          Concatenate (Devices)

          LOAD

               "Device Desktop" as Device,

               Cost

          FROM DevicesDesktopTable;

           

          // This would result in one table.

           

          2. Load the facts and apply the map from the previous table to get the Cost:

           

          DATA:

          LOAD

               CustomerID,

               ItemCount,

               Device,

               Source,

               APPLYMAP('MAP_Devices', Device) as ItemCost,     // just in case you want to keep the individual cost of the device

               APPLYMAP('MAP_Devices', Device) * ItemCount as TotalCost

          FROM datasource;

           

          Hope it helps.