Table transformation

[mario-sarkis]

Hey all hope you can help

this is a sample data that i have i meed to know how can i write in the script the crosstabulation to get this below form.

noting that product field has more then 10 product.

date                      ID               Product

11/6/2016            x                  a

11/6/2016             x                B

2/11/2016.            X               C

3/11/2016             X.                D

11/6/2016.            Y                a

.

.

.

outpout:

date.                 ID.               a.          b.         C.         D 

11/6/2016.        X                  1.          1.          -          -

2/11/2016.        X.                  -           -           1.        -

3/11/2016.        X.                 -           -             -        1

11/6/2016.        Y.                 1.         -            -          -

[sunny_talwar]

You can try Generic LOAD

Table:

GENERIC

LOAD date,

          ID,

          Product

FROM Source;

[sathishkumar_go]

You can try this way

Temp:
LOAD * INLINE [
date,ID,Product
11/6/2016, x, a
11/6/2016, x , B
2/11/2016, X, C
3/11/2016, X., D
11/6/2016, Y , a
];


Temp1:
load
date,
ID,
count(Product) as a
resident Temp where Product = 'a' Group by date,ID ;

concatenate(Temp1)
load
date,
ID,
count(Product) as b
resident Temp where Product = 'B' Group by date,ID ;

concatenate(Temp1)
load
date,
ID,
count(Product) as c
resident Temp where Product = 'C' Group by date,ID ;

drop table Temp;

-Sathish

[settu_periasamy]

Good  explanation here

Use cases for Generic Load | Qlikview Cookbook