topic Transform data in QlikView

Transform data

Wed, 25 Nov 2020 16:16:04 GMT

Good day,

I have a table with 4 variables but I can not use all of them in Qlik View in an appropriate way (while uploading data I have to choose only 2 variables). Is there a way to transform it, cause I have tried a crosstab but it did not work (with crosstab I could transform only 3 variables)? The table is:

Re: Transform data

oknotsen — Sat, 05 Mar 2016 12:02:20 GMT

How about something like this:

TableName:
CrossTable(Price, Quantity, 1)
LOAD 'Company A' as Company,
[10],
[15],
[20]
FROM
Datasources\Budget.xls
(biff, embedded labels, header is 1 lines, table is Sheet1$);

TableName:
CrossTable(Price, Quantity, 1)
LOAD 'Company B' as Company,
[8],
[10],
[15]
FROM
Datasources\Budget.xls
(biff, embedded labels, header is 1 lines, table is Sheet1$);

TableName:
CrossTable(Price, Quantity, 1)
LOAD 'Company C' as Company,
[20]
FROM
Datasources\Budget.xls
(biff, embedded labels, header is 1 lines, table is Sheet1$);

Re: Transform data

Sat, 05 Mar 2016 12:58:22 GMT

okg‌, thanks for the help. Still, I have 1 problem and 1 question. In your example, you haven't mentioned about days? How can I include them too? Also, what shall I do if I have a table with a lot of companies which has a long price range? Should I write script manually?

Re: Transform data

avinashelite — Sat, 05 Mar 2016 13:22:06 GMT

check this:

multi_header_pivot_import.qvw

Re: Transform data

Digvijay_Singh — Sat, 05 Mar 2016 15:55:22 GMT

Check this -

CrossTable(Day, Quantity, 2)

LOAD [Company Name],

Price,

Day1,

Day2,

Day3,

Day4,

Day5,

Day6

FROM

[208302data.xlsx]

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

Transpose(),

Replace(1, top, StrCnd(null))

));

Re: Transform data

oknotsen — Sat, 05 Mar 2016 15:57:41 GMT

My bad... I overlooked that column.

Make this change to your code:

TableName:
CrossTable(Price, Quantity, 2)
LOAD 'Company A' as Company,

Price as Days,

[10],
[15],
[20]
FROM
Datasources\Budget.xls
(biff, embedded labels, header is 1 lines, table is Sheet1$);

So change "1" to "2" and add "Price as Days". And yes, that "Price as Days" looks weird, but it sees Price as the column name (and else you probably get a weird error).