Break up column descriptions into separate columns

[Anonymous]

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,

[JonnyPoole · Accepted solution]

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

[Anonymous]

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

[JonnyPoole]

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

[MarcoWedel]

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