CrossTab table

tmumaw — Wed, 12 Sep 2018 15:37:04 GMT

Hi all,

I need to create a crosstab table which has 2 values (Hours and Dollars). I have created something like this, but the problem is I have 2 values. How can I handle it? Any ideas? WKG...Is Dollars and MEG is Hours

Thanks

[COSSTemp]: // CO Object: Cost Totals for Internal Postings
CrossTable(Period, Values, 21)
Load
[BUKRS] as [COSS_Company Code_BUKRS],
[MEINH] as [COSS_Unit of Measure_MEINH],
[GJAHR] as [COSS_Fiscal Year_GJAHR],
[GEBER] as [COSS_Fund_GEBER],
[FKBER] as [COSS_Functional Area_FKBER],
[OBJNR],
[WRTTP] as [COSS_Value Type_WRTTP],
[VERSN] as [COSS_Version_VERSN],
[GRANT_NBR] as [COSS_Grant_GRANT_NBR],
[SEGMENT] as [COSS_Segment_SEGMENT],
[KSTAR] as [COSS_Cost Element_KSTAR],
[LEDNR] as [COSS_Ledger_LEDNR],
[HRKFT] as [COSS_CO subkey_HRKFT],
[VRGNG] as [COSS_Bus.Transaction_VRGNG],
[PAROB] as [COSS_Partner Object_PAROB],
[USPOB] as [COSS_Source Object_USPOB],
[BEKNZ] as [COSS_Dr/Cr indicator_BEKNZ],
[BELTP] as [COSS_Debit type_BELTP],
[TIMESTMP] as [COSS_Time created_TIMESTMP],
[TWAER] as [COSS_Trans. Currency_TWAER],
[PERBL] as [COSS_Period block_PERBL],
[WKG001] as [001],
[WKG002] as [002],
[WKG003] as [003],
[WKG004] as [004],
[WKG005] as [005],
[WKG006] as [006],
[WKG007] as [007],
[WKG008] as [008],
[WKG009] as [009],
[WKG010] as [010],
[WKG011] as [011],
[WKG012] as [012],
[WKG013] as [013],
[WKG014] as [014],
[WKG015] as [015],
[WKG016] as [016],
[MEG001] as [001],
[MEG002] as [002],
[MEG003] as [003],
[MEG004] as [004],
[MEG005] as [005],
[MEG006] as [006],
[MEG007] as [007],
[MEG008] as [008],
[MEG009] as [009],
[MEG010] as [010],
[MEG011] as [011],
[MEG012] as [012],
[MEG013] as [013],
[MEG014] as [014],
[MEG015] as [015],
[MEG016] as [016]
;
SQL Select MANDT BUKRS MEINH OBJNR GJAHR HRKFT GEBER FKBER WRTTP VERSN GRANT_NBR SEGMENT
KSTAR LEDNR VRGNG PAROB USPOB BEKNZ BELTP TIMESTMP TWAER PERBL
WKG001 WKG002 WKG003 WKG004 WKG005 WKG006 WKG007 WKG008 WKG009 WKG010 WKG011 WKG012 WKG013 WKG014 WKG015 WKG016
MEG001 MEG002 MEG003 MEG004 MEG005 MEG006 MEG007 MEG008 MEG009 MEG010 MEG011 MEG012 MEG013 MEG014 MEG015 MEG016
from COSS
Where GJAHR >= '$(vCurrentYear)'

Re: CrossTab table

rwunderlich — Wed, 12 Sep 2018 17:57:42 GMT

Two Loads, first for WKG fields, second for MEG fields, same 21 qualifying fields each time. Then Join the two tables together. Don't try to do this with preceding load. Create two tables, then join.

-Rob

http://masterssummit.com

http://qlikviewcookbook.com

http://www.easyqlik.com

Re: CrossTab table

tmumaw — Wed, 12 Sep 2018 18:14:09 GMT

Do you see any reason Temp3 table would not be found? thanks

[Temp]: // CO Object: Cost Totals for Internal Postings
Load
//BEKNZ & GJAHR & HRKFT & KSTAR & LEDNR & MANDT & OBJNR & PAROB & PERBL & TWAER & USPOB & VERSN & VRGNG & WRTTP AS %KEY_COSS_BEKNZ_GJAHR_HRKFT_KSTAR_LEDNR_MANDT_OBJNR_PAROB_PERBL_TWAER_USPOB_VERSN_VRGNG_WRTTP,
[BUKRS] as [COSS_Company Code_BUKRS],
[MEINH] as [COSS_Unit of Measure_MEINH],
[GJAHR] as [COSS_Fiscal Year_GJAHR],
[GEBER] as [COSS_Fund_GEBER],
[FKBER] as [COSS_Functional Area_FKBER],
[OBJNR],// as [COSS_Object number_OBJNR],
[WRTTP] as [COSS_Value Type_WRTTP],
[VERSN] as [COSS_Version_VERSN],
[GRANT_NBR] as [COSS_Grant_GRANT_NBR],
[SEGMENT] as [COSS_Segment_SEGMENT],
[KSTAR] as [COSS_Cost Element_KSTAR],
[LEDNR] as [COSS_Ledger_LEDNR],
[HRKFT] as [COSS_CO subkey_HRKFT],
[VRGNG] as [COSS_Bus.Transaction_VRGNG],
[PAROB] as [COSS_Partner Object_PAROB],
[USPOB] as [COSS_Source Object_USPOB],
[BEKNZ] as [COSS_Dr/Cr indicator_BEKNZ],
[BELTP] as [COSS_Debit type_BELTP],
[TIMESTMP] as [COSS_Time created_TIMESTMP],
[TWAER] as [COSS_Trans. Currency_TWAER],
[PERBL] as [COSS_Period block_PERBL],
[WKG001] as [Val/COArea Crcy_WKG001],
[WKG002] as [Val/COArea Crcy_WKG002],
[WKG003] as [Val/COArea Crcy_WKG003],
[WKG004] as [Val/COArea Crcy_WKG004],
[WKG005] as [Val/COArea Crcy_WKG005],
[WKG006] as [Val/COArea Crcy_WKG006],
[WKG007] as [Val/COArea Crcy_WKG007],
[WKG008] as [Val/COArea Crcy_WKG008],
[WKG009] as [Val/COArea Crcy_WKG009],
[WKG010] as [Val/COArea Crcy_WKG010],
[WKG011] as [Val/COArea Crcy_WKG011],
[WKG012] as [Val/COArea Crcy_WKG012],
[WKG013] as [Val/COArea Crcy_WKG013],
[WKG014] as [Val/COArea Crcy_WKG014],
[WKG015] as [Val/COArea Crcy_WKG015],
[WKG016] as [Val/COArea Crcy_WKG016],
[MEG001] as [Total Quantity_MEG001],
[MEG002] as [Total Quantity_MEG002],
[MEG003] as [Total Quantity_MEG003],
[MEG004] as [Total Quantity_MEG004],
[MEG005] as [Total Quantity_MEG005],
[MEG006] as [Total Quantity_MEG006],
[MEG007] as [Total Quantity_MEG007],
[MEG008] as [Total Quantity_MEG008],
[MEG009] as [Total Quantity_MEG009],
[MEG010] as [Total Quantity_MEG010],
[MEG011] as [Total Quantity_MEG011],
[MEG012] as [Total Quantity_MEG012],
[MEG013] as [Total Quantity_MEG013],
[MEG014] as [Total Quantity_MEG014],
[MEG015] as [Total Quantity_MEG015],
[MEG016] as [Total Quantity_MEG016]
;
SQL Select MANDT BUKRS MEINH OBJNR GJAHR HRKFT GEBER FKBER WRTTP VERSN GRANT_NBR SEGMENT
KSTAR LEDNR VRGNG PAROB USPOB BEKNZ BELTP TIMESTMP TWAER PERBL WKG001 WKG002 WKG003
WKG004 WKG005 WKG006 WKG007 WKG008 WKG009 WKG010 WKG011 WKG012 WKG013 WKG014
WKG015 WKG016 MEG001 MEG002 MEG003 MEG004 MEG005 MEG006 MEG007 MEG008 MEG009
MEG010 MEG011 MEG012 MEG013 MEG014 MEG015 MEG016
from COSS
Where GJAHR >= '$(vCurrentYear)'
;

NoConcatenate
[Temp1]:
Load *,
if([Total Quantity_MEG001] > 0 or [Total Quantity_MEG002] > 0 or [Total Quantity_MEG003] > 0 or [Total Quantity_MEG004] > 0 or
[Total Quantity_MEG005] > 0 or [Total Quantity_MEG006] > 0 or [Total Quantity_MEG007] > 0 or [Total Quantity_MEG008] > 0 or
[Total Quantity_MEG009] > 0 or [Total Quantity_MEG010] > 0 or [Total Quantity_MEG011] > 0 or [Total Quantity_MEG012] > 0 or
[Total Quantity_MEG013] > 0 or [Total Quantity_MEG014] > 0 or [Total Quantity_MEG015] > 0 or [Total Quantity_MEG016] > 0,'H',' ') as HasHrs
RESIDENT Temp
WHERE exists(PRPS_OBJNR,OBJNR)
;

[Temp2]: // CO Object: Cost Totals for Internal Postings
CrossTable(Period, Values, 22)
Load
[COSS_Company Code_BUKRS],
[COSS_Unit of Measure_MEINH],
[COSS_Fiscal Year_GJAHR],
[COSS_Fund_GEBER],
[COSS_Functional Area_FKBER],
[OBJNR],
[COSS_Value Type_WRTTP],
[COSS_Version_VERSN],
[COSS_Grant_GRANT_NBR],
[COSS_Segment_SEGMENT],
[COSS_Cost Element_KSTAR],
[COSS_Ledger_LEDNR],
[COSS_CO subkey_HRKFT],
[COSS_Bus.Transaction_VRGNG],
[COSS_Partner Object_PAROB],
[COSS_Source Object_USPOB],
[COSS_Dr/Cr indicator_BEKNZ],
[COSS_Debit type_BELTP],
[COSS_Time created_TIMESTMP],
[COSS_Trans. Currency_TWAER],
[COSS_Period block_PERBL],
HasHrs,
[Val/COArea Crcy_WKG001] as 001,
[Val/COArea Crcy_WKG002] as 002,
[Val/COArea Crcy_WKG003] as 003,
[Val/COArea Crcy_WKG004] as 004,
[Val/COArea Crcy_WKG005] as 005,
[Val/COArea Crcy_WKG006] as 006,
[Val/COArea Crcy_WKG007] as 007,
[Val/COArea Crcy_WKG008] as 008,
[Val/COArea Crcy_WKG009] as 009,
[Val/COArea Crcy_WKG010] as 010,
[Val/COArea Crcy_WKG011] as 011,
[Val/COArea Crcy_WKG012] as 012,
[Val/COArea Crcy_WKG013] as 013,
[Val/COArea Crcy_WKG014] as 014,
[Val/COArea Crcy_WKG015] as 015,
[Val/COArea Crcy_WKG016] as 016
RESIDENT Temp1
;

NoConcatenate
[COSS]:
Load *,
if ([COSS_Value Type_WRTTP]='01',Values,0) as [Planned Cost],
if ([COSS_Value Type_WRTTP]='04',Values,0) as [Actual Cost],
if (HasHrs = 'H',Values,0) as Hours
RESIDENT Temp2
;
STORE * FROM [COSS] INTO QVD\COSS.QVD;

[Temp3]: // CO Object: Cost Totals for Internal Postings
CrossTable(Period, Values, 22)
Load
[COSS_Company Code_BUKRS],
[COSS_Unit of Measure_MEINH],
[COSS_Fiscal Year_GJAHR],
[COSS_Fund_GEBER],
[COSS_Functional Area_FKBER],
[OBJNR],
[COSS_Value Type_WRTTP],
[COSS_Version_VERSN],
[COSS_Grant_GRANT_NBR],
[COSS_Segment_SEGMENT],
[COSS_Cost Element_KSTAR],
[COSS_Ledger_LEDNR],
[COSS_CO subkey_HRKFT],
[COSS_Bus.Transaction_VRGNG],
[COSS_Partner Object_PAROB],
[COSS_Source Object_USPOB],
[COSS_Dr/Cr indicator_BEKNZ],
[COSS_Debit type_BELTP],
[COSS_Time created_TIMESTMP],
[COSS_Trans. Currency_TWAER],
[COSS_Period block_PERBL],
HasHrs,
[Total Quantity_MEG001] as 001,
[Total Quantity_MEG002] as 002,
[Total Quantity_MEG003] as 003,
[Total Quantity_MEG004] as 004,
[Total Quantity_MEG005] as 005,
[Total Quantity_MEG006] as 006,
[Total Quantity_MEG007] as 007,
[Total Quantity_MEG008] as 008,
[Total Quantity_MEG009] as 009,
[Total Quantity_MEG010] as 010,
[Total Quantity_MEG011] as 011,
[Total Quantity_MEG012] as 012,
[Total Quantity_MEG013] as 013,
[Total Quantity_MEG014] as 014,
[Total Quantity_MEG015] as 015,
[Total Quantity_MEG016] as 016
RESIDENT Temp1
;

NoConcatenate
[COSSHR]:
Load *,
if ([COSS_Value Type_WRTTP]='01',Values,0) as [Planned Hours],
if ([COSS_Value Type_WRTTP]='04',Values,0) as [Actual Hours],
if (HasHrs = 'H',Values,0) as Hours
RESIDENT Temp3
;
STORE * FROM [COSSHR] INTO QVD\COSSHR.QVD;

DROP tables Temp, Temp1, Temp2, Temp3;

exit SCRIPT;

topic Re: CrossTab table in QlikView

CrossTab table

Re: CrossTab table

Re: CrossTab table