Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Announcements

SYSTEM MAINTENANCE: ** Thurs., Sept. 19, 1 AM ET,** Platform will be unavailable for approx. 60 minutes.

- Qlik Community
- :
- All Forums
- :
- QlikView App Dev
- :
- CrossTab table

Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

tmumaw

Specialist II

2018-09-12
11:37 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

CrossTab table

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)'

- Tags:
- script logic

455 Views

2 Replies

rwunderlich

Partner Ambassador/MVP

2018-09-12
01:57 PM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

tmumaw

Specialist II

2018-09-12
02:14 PM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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**;

415 Views