topic Break up column descriptions into separate columns in QlikView

Break up column descriptions into separate columns

Anonymous — Tue, 02 Sep 2014 20:30:26 GMT

In the attached table I need to break out the Other Charges categories into their own columns.

Example: Brooder Transfer Catching Drugs Hauling

0.13 0.51 0.23 0.72

Any help on this will be greatly appreciated.

Thanks,

Re: Break up column descriptions into separate columns

JonnyPoole — Tue, 02 Sep 2014 20:45:55 GMT

Few options:

1. Use a pivot table and drag/drop the 'other charges' field as column headers

2. Use set analysis to create a new expression for each possible other charge one by one (manual effort)

Sum( {$<[Other Charges] = {'Brooder Transfer'}>} Sales

3. Use a GENERIC load in the load script to pivot the distinct row values in 'other charges' as brand new fields

If you need an example of 3 , i can mock one up

Re: Break up column descriptions into separate columns

Anonymous — Tue, 02 Sep 2014 20:55:27 GMT

Jonathan,

Three seems like a more permanent solution to the issue as I will need the amounts in other objects in the future. An example would help if you have time and can work one up.

Thanks,

Bradley

Re: Re: Break up column descriptions into separate columns

JonnyPoole — Tue, 02 Sep 2014 21:06:23 GMT

i like the pivot table the most actually beacuse its completely dynamic. although it depends if you are constrained by formatting options. generic load seems a little less manual than set anaysis but still manual

Re: Break up column descriptions into separate columns

MarcoWedel — Tue, 02 Sep 2014 21:41:56 GMT

Hi,

if you would like to go for the generic load solution, maybe this example helps also:

table1:
Generic LOAD *
From [http://community.qlik.com/servlet/JiveServlet/download/600114-123026/QlikCommunity_Thread_131639.xls]
(biff, embedded labels, table is Sheet1$);

table2:
LOAD Distinct
  Grower, 
  [Project #]
From [http://community.qlik.com/servlet/JiveServlet/download/600114-123026/QlikCommunity_Thread_131639.xls]
(biff, embedded labels, table is Sheet1$);

FOR i = NoOfTables()-1 to 0 STEP -1
  LET vTable=TableName($(i));
  IF WildMatch('$(vTable)', 'table1.*') THEN
  LEFT JOIN (table2) LOAD * RESIDENT [$(vTable)];
  DROP TABLE [$(vTable)];
  ENDIF
NEXT i

This script uses a method described by Rob Wunderlich to combine tables created by a generic load:

http://qlikviewnotes.blogspot.de/2010/05/use-cases-for-generic-load.html

Also one fine post regarding the combination of tables created by a generic load (by Henric Cronström):

http://community.qlik.com/blogs/qlikviewdesignblog/2014/03/31/generic

hope this helps

regards

Marco