topic Re: Cross table load script in QlikView

Cross table load script

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

I have a source table in the below format

and i want to use cross table load to change it to the below format

what can i do to achieve the above? thanks

Re: Cross table load script

john_obrien — Mon, 19 Feb 2018 03:59:32 GMT

Ronald,

You could load the first 3 columns using the crosstable load and make a field called product with value of 'A'. Then concatenate load columns 4 to 6 using the same technique, but with the value 'B' for the product field.

I hope that helps.

Re: Cross table load script

sunny_talwar — Mon, 19 Feb 2018 15:18:58 GMT

May be this

Table:

CrossTable(Date, Value, 2)

LOAD ProductA as Product,

Date as Category,

[43101.000000],

[43102.000000],

[43103.000000],

[43104.000000]

FROM

[..\..\..\Downloads\Book12.xlsx]

(ooxml, embedded labels, table is Sheet3, filters(

Transpose(),

Replace(1, top, StrCnd(null))

));

FinalTable:

LOAD DISTINCT Product,

Date(Num#(Date)) as Date

Resident Table;

FOR i = 1 to FieldValueCount('Category')

LET vCategory = FieldValue('Category', $(i));

If '$(vCategory)' <> 'Date' then

Left Join (FinalTable)

LOAD Product,

Date(Num#(Date)) as Date,

Num(Num#(Value)) as [$(vCategory)]

Resident Table

Where Category = '$(vCategory)';

ENDIF

NEXT

DROP Table Table;

Re: Cross table load script

ronaldwang — Mon, 19 Feb 2018 22:36:25 GMT

thanks, it is nice

Re: Cross table load script

kamikhan — Thu, 24 Oct 2019 07:41:01 GMT

hi i have a data in mysql i want to use cross table to load data . but i didn't found any solution.if you have a solution so kindly reply .