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]

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.

[Anonymous]

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.

[engishfaque]

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

[engishfaque]

Hi Sheng,

Kindly find attached App.

Kind regards,

Ishfaque Ahmed

[Anonymous]

Hi, Based on the scenario,

Group	Personal 1	Personal 2	Personal 3	Money
A	Name1	Name2	Name3	107

[Anonymous]

Hi, I am unable to open ur attachment.

[engishfaque]

Hi Sheng,

You are using QlikView Personal Edition therefore, you are not able to open attached document.

Kind regards,

Ishfaque Ahmed

[MarcoWedel]

isn't that different from what you did with the money field in your first example?

[MarcoWedel]

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