Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
May 18th 10AM ET, Live Chat, bring your QlikView questions. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
hobanwashburne
Creator
Creator

Odd Database format

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?

1 Solution

Accepted Solutions
nagaiank
Specialist III
Specialist III

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.

View solution in original post

3 Replies
Not applicable

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

marcarreras
Specialist
Specialist

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
Specialist III
Specialist III

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.

View solution in original post