Discussion Board for collaboration related to QlikView App Development.
I'm working with a database that contains discount codes for product lines by customer. However the format is as follows:
Customer, Product Line 1, Discount 1, Product Line 2, Discount 2,...
where for Customer A Product Line 1 is Milk and Product Line 2 is Eggs,
but for Customer B Product Line 1 is Eggs and Product Line 2 is Milk,
and for Customer C Product Line 1 is Bread and there is no Product Line 2
How can I normalize this data?
Use a script similar to the following:
, [Product Line 1] as Product
, [Discount 1] as Discount
Where Len(Trim([Product Line 1])) > 0;
, [Product Line 2] as Product
, [Discount 2] as Discount
Where Len(Trim([Product Line 2])) > 0;
Hope this helps.
View solution in original post
They is a concept of GENERIC LOAD which is reverse of crosstab. I think you can achieve using that.
Load first product1, then product2...
load Customer, ProductLine1 as Product, Discount1 as Discount, 1 as ProductNumber from 'your source';
load Customer, ProductLine2 as Product, Discount2 as Discount, 2 as ProductNumber from 'your source';
...add more if more products columns
You will obtain a table with concatenated customers, products and discounts.
Hope it helps.