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 |
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
That doesn't seem to be the same data. I'd think this would be the same data with the arrangement changed:
Group, Name1, Name2, Name3
A, 60, 60, 60
Which would only require the GENERIC prefix in front of your load.
If you really want what you showed, then I don't understand what you're doing with Money. Is it considered a key field? Avg()? Max()? How are you combining three values of 60 into a single value of 60? I'm leaving Money off the table until I can understand it.
Data:
GENERIC LOAD
Group
,'Personal' & right(Personal,1) as Personal
,Personal as Value
INLINE [
Group, Personal
A,Name1
A,Name2
A,Name3
];
I'm sure that's not how you want to assign the Personal field names, but it's unclear to me how you want them assigned, so I just went with something that produces your result. Technically a generic load does not produce the table structure you asked for, just the logical equivalent. All the individual tables can be collapsed into one, but this is probably unnecessary. You should get the desired results without doing so. Unless you want to store to a QVD, in which case you'd want to do that.
Sry my bad, The money is not an key field. It just one of the field that I have. And for the multiple $60 into a single $60, think if it as the group have $60 (not shared equally) but the data put it as such (multiple $60). Hope it clears up.
Hi Sheng,
Let me clear your scenario, if we have listed below scenario then what would be the expected output or desired output you want?
[My Data]:
Load * Inline [
Group, Personal, Money
A, Name1, 30
A, Name2, 50
A, Name3, 27
];
Kind regards,
Ishfaque Ahmed
Hi Sheng,
Kindly find attached App.
Kind regards,
Ishfaque Ahmed
Hi, Based on the scenario,
Group | Personal 1 | Personal 2 | Personal 3 | Money |
---|---|---|---|---|
A | Name1 | Name2 | Name3 | 107 |
Hi, I am unable to open ur attachment.
Hi Sheng,
You are using QlikView Personal Edition therefore, you are not able to open attached document.
Kind regards,
Ishfaque Ahmed
isn't that different from what you did with the money field in your first example?
Hi,
one example using John's generic load solution, your specified money aggregation method and Rob Wunderlich's code for generic table recombinations would be:
MyData:
LOAD * INLINE [
Group, Personal, Money
A, Alice, 12
A, Bob, 23
A, Charlie, 34
B, Dave, 45
B, Eve, 56
B, Frank, 67
C, Grace, 78
C, Harry, 89
C, Ivy, 90
];
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
I used AutoNumber(Personal,Group) to create a Personal#, supposing your actual Personal is not named like Name1, Name2, Name3
hope this helps
regards
Marco