topic Re: Odd Database format in QlikView

Odd Database format

hobanwashburne — Wed, 01 Oct 2014 15:59:17 GMT

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?

Wed, 01 Oct 2014 16:03:38 GMT

They is a concept of GENERIC LOAD which is reverse of crosstab. I think you can achieve using that.

Anonymous — Wed, 01 Oct 2014 16:03:50 GMT

Scott,

Load first product1, then product2...

table:

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.

Marc.

nagaiank — Wed, 01 Oct 2014 16:05:38 GMT

Use a script similar to the following:

FinalTable:

LOAD Customer

, [Product Line 1] as Product

, [Discount 1] as Discount

From YourTable..

Where Len(Trim([Product Line 1])) > 0;

Concatenate (FinalTable)

LOAD Customer

, [Product Line 2] as Product

, [Discount 2] as Discount

From YourTable..

Where Len(Trim([Product Line 2])) > 0;

Hope this helps.