Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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,
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
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
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
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
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