topic Re: change arrangement in QlikView

change arrangement

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: change arrangement

johnw — Fri, 28 Oct 2016 23:49:06 GMT

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.

Re: change arrangement

Anonymous — Mon, 31 Oct 2016 00:48:23 GMT

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.

Re: change arrangement

engishfaque — Mon, 31 Oct 2016 04:46:53 GMT

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

Re: change arrangement

engishfaque — Mon, 31 Oct 2016 05:03:43 GMT

Hi Sheng,

Kindly find attached App.

Kind regards,

Ishfaque Ahmed

Re: change arrangement

Anonymous — Tue, 01 Nov 2016 00:39:13 GMT

Hi, Based on the scenario,

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

Re: change arrangement

Anonymous — Tue, 01 Nov 2016 00:40:51 GMT

Hi, I am unable to open ur attachment.

Re: change arrangement

engishfaque — Tue, 01 Nov 2016 17:45:47 GMT

Hi Sheng,

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

Kind regards,

Ishfaque Ahmed

Re: change arrangement

MarcoWedel — Tue, 01 Nov 2016 21:04:42 GMT

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

Re: change arrangement

MarcoWedel — Tue, 01 Nov 2016 21:41:35 GMT

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

Re: change arrangement

johnw — Tue, 01 Nov 2016 22:18:30 GMT

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

Re: change arrangement

engishfaque — Wed, 02 Nov 2016 03:42:20 GMT

Hi Sheng,

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

Kind regards,

Ishfaque Ahmed

Re: change arrangement

Anonymous — Wed, 02 Nov 2016 08:01:25 GMT

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.

Re: change arrangement

MarcoWedel — Wed, 02 Nov 2016 20:35:06 GMT

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

Re: change arrangement

Anonymous — Fri, 04 Nov 2016 01:10:27 GMT

HI, my excel example.

Re: change arrangement

MarcoWedel — Sun, 06 Nov 2016 20:30:35 GMT

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

Re: change arrangement

Anonymous — Mon, 07 Nov 2016 01:49:11 GMT

HI Marco,

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

Re: change arrangement

MarcoWedel — Mon, 07 Nov 2016 20:47:44 GMT

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

regards

Marco

Re: change arrangement

Anonymous — Tue, 08 Nov 2016 01:30:52 GMT

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.

Re: change arrangement

MarcoWedel — Tue, 08 Nov 2016 20:25:39 GMT

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