Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
HI, I have a data of rows and column that I would like to change arrangement.
My Data
Load* Inline:
[
Group, Personal, Money
A,Name1,60
A,Name2,60
A,Name3,60
]
What I like to display in my table:
Group | Personal 1 | Personal 2 | Personal 3 | Money |
---|---|---|---|---|
A | Name1 | Name2 | Name3 | 60 |
For some reason I can't orient it horizontally, but I got the same result with just the raw data and a pivot table.
Dimension 1 = =aggr('Personal' & rank(Personal),Group,Personal)
Dimension 2 = Group
Dimension 3 = =aggr(sum(Money),Group)
Expression = Personal
Hi Sheng,
I am attaching script (Data Model) and output screenshot, please find attached file.
Kind regards,
Ishfaque Ahmed
Hi, my data come from excel actually and not inline, how am I able to have it work? I script the solution(changed to the proper variables) after loading the excel file and it show error of table not found.
can you post a sample of your Excel file to demonstrate?
HI, my excel example.
Hi,
replacing the inline load with loading your excel could look like:
MyData:
LOAD * FROM [https://community.qlik.com/servlet/JiveServlet/download/1154131-252295/Example.xlsx] (ooxml, embedded labels, table is Sheet1);
tabTemp:
Generic
LOAD Group,
'Personal '&AutoNumber(Personal,Group),
Personal
Resident MyData;
MyTable:
LOAD Group,
Sum(Money) as Money
Resident MyData
Group By Group;
DROP Table MyData;
FOR i = NoOfTables()-1 to 0 STEP -1
LET vTable=TableName($(i));
IF WildMatch('$(vTable)', 'tabTemp.*') THEN
LEFT JOIN (MyTable) LOAD * RESIDENT [$(vTable)];
DROP TABLE [$(vTable)];
ENDIF
NEXT i
hope this helps
regards
Marco
HI Marco,
I tried to run the script and it say table no found.
can you upload a sample of your application to demonstrate what does not work?
regards
Marco
Hi, looks like I left some fields I did not change, thus the error. But although it works, it screws up my other applications because the other field of data within the excel "disappear".
For example from the attachments, I add payment and dates data into my excel, but after loading the script, both data, disappear. Also, even [personal] disappear as well, as I might need to use it for other applications.
Hi,
just delete the "drop table MyData" statement to keep your original table including the required fields.
MyData:
LOAD Group,
Personal,
Money,
Payment,
Date
FROM
[https://community.qlik.com/servlet/JiveServlet/download/1155663-252724/Example.xlsx]
(ooxml, embedded labels, table is Sheet1);
tabTemp:
Generic
LOAD Group,
'Personal '&AutoNumber(Personal,Group),
Personal
Resident MyData;
MyTable:
LOAD Group,
Sum(Money) as SMoney
Resident MyData
Group By Group;
FOR i = NoOfTables()-1 to 0 STEP -1
LET vTable=TableName($(i));
IF WildMatch('$(vTable)', 'tabTemp.*') THEN
LEFT JOIN (MyTable) LOAD * RESIDENT [$(vTable)];
DROP TABLE [$(vTable)];
ENDIF
NEXT i
hope this helps
regards
Marco