topic Re: Cross Table in QlikView

Cross Table

thannila — Wed, 25 Nov 2020 16:16:04 GMT

Table:

LOAD * INLINE [

Product, Month, Sales

A, Jan 2017, 10

A, Mar 2017, 20

A, Apr 2017, 30

B, Mai 2017, 40

B, Jun 2017, 50

];

FinalTable:

LOAD DISTINCT Product

Resident Table;

For i = 1 to FieldValueCount('Month')

LET vField = FieldValue('Month', $(i));

Left Join (FinalTable)

LOAD DISTINCT Product,

Sales as [$(vField)]

Resident Table

Where Month = '$(vField)';

Next

DROP Table Table;

can anyone explain this code?

For i = 1 to FieldValueCount('Month')

LET vField = FieldValue('Month', $(i));

Left Join (FinalTable)

LOAD DISTINCT Product,

Sales as [$(vField)]

Resident Table

Where Month = '$(vField)';

My doubt is how we are getting the sales value here?

Re: Cross Table

olivierrobin — Thu, 12 Jul 2018 12:46:32 GMT

hello

for i=1 to number of values of field month

vField=value of ith occurence of month

left join existing table and adding new column named with the month name and containing the value of the month

at the end, on each row, you have 5 more columns named Jan 2017, ... Jun 2017

Re: Cross Table

thannila — Thu, 12 Jul 2018 12:54:44 GMT

But how we are getting the sales value???

Re: Cross Table

olivierrobin — Thu, 12 Jul 2018 12:56:26 GMT

here

Sales as [$(vField)]

the field Sales is left joined under the name contained in the field vField

Re: Cross Table

thannila — Thu, 12 Jul 2018 13:19:19 GMT

Thank you for your explanation.But instead of using this we can use generic load.

Re: Cross Table

olivierrobin — Thu, 12 Jul 2018 13:21:06 GMT

generic load will create 1 table per month value

all depends on your needs