Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
AWS Degraded - You may experience Community slowness, timeouts, or trouble accessing: LATEST HERE
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Transform data

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:

ex1.PNG

5 Replies
oknotsen
Master III
Master III

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$);

May you live in interesting times!
Not applicable
Author

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?

avinashelite

Digvijay_Singh

Check this -

Capture.JPG

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))

));

oknotsen
Master III
Master III

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).

May you live in interesting times!