Working with pre-summarised data

[#1]

Hi Everyone,

I've been given a flat file containing campaign response data. It has a few columns which represent dimension fields, which break down each campaign into rows for various combinations of the dimensions (control vs treatment, different demographics targeted within the campaign etc). It then has approximately 600 columns containing numerical values representing the number of sales for each product. E.G PRODUCT_1, PRODUCT_2, PRODUCT_3... PRODUCT_600.

I've been asked to create a dashboard using this data. I've managed to get a simple sales table for treatment vs control by using a Calculated Dimension of "IF(CONTROL =1,'Control','Treatment')" and having a dimension for each product of "SUM(PRODUCT_1)",  "SUM(PRODUCT_2)"... "SUM(PRODUCT_600)".  and then making the table a "Horizontal" table

Using this method, I can't create any derived columns or do any calculations on the product columns

Has anyone worked with data structured in this way before? Is there anything I can do (maybe at the scripting stage) that will help get the data into a format that Qlikview will like? Or is there a better way of working with this data than the method I have mentioned above (which isn't working very well!)

Any help would be greatly appreciated!

[geert_gelade · Accepted solution]

Maybe you can use the crosstable load in the script to convert your data.

Can you upload a sample file with your current data ?

[Anonymous]

Hi,

You need turn your data with Transpose function in the loading script, like

LOAD @1,

     @2

FROM

(ooxml, explicit labels, table is Sheet1, filters(

Transpose()

));

Sample files attached

[#4]

Thanks Geert. That was exactly what was required.

I ended up reading the same table twice in the Load phase. The first time I loaded only the dimension fields and (pre-summarised) counts of customers targeted. The second time (loading from the same table) I did a cross table load, including only the dimensions required to form a composite key to the first load and the 600 products (starting the pivot when I go to the first product column). Qlikview automatically recognised and implemented the composite key between the two passes (and thus two tables) of the data source.

Thanks!