Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Break up column descriptions into separate columns

Screenshot.png

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,

1 Solution

Accepted Solutions
JonnyPoole
Former Employee
Former Employee

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

View solution in original post

4 Replies
JonnyPoole
Former Employee
Former Employee

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
Not applicable
Author

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
Former Employee
Former Employee

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:

QlikCommunity_Thread_131639_Pic1.JPG.jpg

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