change arrangement

[Anonymous]

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

[johnw]

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

[engishfaque]

Hi Sheng,

I am attaching script (Data Model) and output screenshot, please find attached file.

Kind regards,

Ishfaque Ahmed

[Anonymous]

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.

[MarcoWedel]

can you post a sample of your Excel file to demonstrate?

[Anonymous]

HI, my excel example.

[MarcoWedel]

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

[Anonymous]

HI Marco,

I tried to run the script and it say table no found.

[MarcoWedel]

can you upload a sample of your application to demonstrate what does not work?

regards

Marco

[Anonymous]

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.

[MarcoWedel · Accepted solution]

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