topic Preceding Load in QlikView

Preceding Load

AC3 — Fri, 07 Aug 2015 17:49:00 GMT

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!

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

Fri, 07 Aug 2015 20:00:30 GMT

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.

Re: Preceding Load

AC3 — Fri, 07 Aug 2015 21:56:28 GMT

Borja, Thank you so much for the suggestion. My mapping table is more complex than I stated, but this give me some ideas to try out.