topic Cross table help for two crosstable() in QlikView

Cross table help for two crosstable()

Tue, 17 Nov 2015 10:43:13 GMT

Dear Expert

Help me to get cross table at once for below script. But it load only CrossTable(Month, Amount, 27) and skip CrossTable(Month, QTY , 8).

How do i get desired result. and get cross table in single table.

Thanks

Ashif

CrossTable(Month, QTY , 😎

CrossTable(Month, Amount, 27)

LOAD

POSKI AS B_WBS,

PSPID,

KALNR,

POSNR,

GJAHR,

VERSN,

WBS_DES AS DESC1,

LTEXT AS B_DESC,

PMG001,

PMG002,

PMG003,

PMG004,

PMG005,

PMG006,

PMG007,

PMG008,

PMG009,

PMG010,

PMG011,

PMG012,

PMEHT,

PMG,

KSTAR,

KATYP,

GPREIS,

FWAER,

GPREIS1,

PMGC01,

PMGC02,

PMGC03,

PMGC04,

PMGC05,

PMGC06,

PMGC07,

PMGC08,

PMGC09,

PMGC10,

PMGC11,

PMGC12,

PMGR01,

PMGR02,

PMGR03,

PMGR04,

PMGR05,

PMGR06,

PMGR07,

PMGR08,

PMGR09,

PMGR10,

PMGR11,

PMGR12

FROM

.....QVD;

Re: Cross table help for two crosstable()

Anonymous — Tue, 17 Nov 2015 10:46:54 GMT

Try to reload it twice as you are formatting it into 2 different ways:

Table2:

CrossTable(Month, QTY , 😎

LOAD

POSKI AS B_WBS,

PSPID,

KALNR,

POSNR,

GJAHR,

VERSN,

WBS_DES AS DESC1,

LTEXT AS B_DESC,

PMG001,

PMG002,

PMG003,

PMG004,

PMG005,

PMG006,

PMG007,

PMG008,

PMG009,

PMG010,

PMG011,

PMG012,

PMEHT,

PMG,

KSTAR,

KATYP,

GPREIS,

FWAER,

GPREIS1,

PMGC01,

PMGC02,

PMGC03,

PMGC04,

PMGC05,

PMGC06,

PMGC07,

PMGC08,

PMGC09,

PMGC10,

PMGC11,

PMGC12,

PMGR01,

PMGR02,

PMGR03,

PMGR04,

PMGR05,

PMGR06,

PMGR07,

PMGR08,

PMGR09,

PMGR10,

PMGR11,

PMGR12

FROM

.....QVD;

Table1:

CrossTable(Month, Amount, 27)

LOAD

POSKI AS B_WBS,

PSPID,

KALNR,

POSNR,

GJAHR,

VERSN,

WBS_DES AS DESC1,

LTEXT AS B_DESC,

PMG001,

PMG002,

PMG003,

PMG004,

PMG005,

PMG006,

PMG007,

PMG008,

PMG009,

PMG010,

PMG011,

PMG012,

PMEHT,

PMG,

KSTAR,

KATYP,

GPREIS,

FWAER,

GPREIS1,

PMGC01,

PMGC02,

PMGC03,

PMGC04,

PMGC05,

PMGC06,

PMGC07,

PMGC08,

PMGC09,

PMGC10,

PMGC11,

PMGC12,

PMGR01,

PMGR02,

PMGR03,

PMGR04,

PMGR05,

PMGR06,

PMGR07,

PMGR08,

PMGR09,

PMGR10,

PMGR11,

PMGR12

FROM

.....QVD;

Re: Cross table help for two crosstable()

Tue, 17 Nov 2015 10:50:58 GMT

Need in single table and join also not possible.

Re: Cross table help for two crosstable()

jonathandienst — Tue, 17 Nov 2015 11:00:55 GMT

As I see it, there are three crosstables (three sets of 12 periods), with QTY, Amount and one other value (which I called Other in this case). You will need to perform the cross tables separately and join the results, like this script below. The 3 crosstable loads load from the same QVD. I have also ensured that the periods align in the join (as Pnn):

Table1:

CrossTable(Month, QTY , 15)

LOAD

POSKI AS B_WBS,

PSPID,

KALNR,

POSNR,

GJAHR,

VERSN,

WBS_DES AS DESC1,

LTEXT AS B_DESC,

PMEHT,

PMG,

KSTAR,

KATYP,

GPREIS,

FWAER,

GPREIS1,

PMG001 As P01,

PMG002 As P02,

PMG003 As P03,

PMG004 As P04,

PMG005 As P05,

PMG006 As P06,

PMG007 As P07,

PMG008 As P08,

PMG009 As P09,

PMG010 As P10,

PMG011 As P11,

PMG012 As P12

FROM

.....QVD;

Table2:

CrossTable(Month, Amount , 15)

LOAD

POSKI AS B_WBS,

PSPID,

KALNR,

POSNR,

GJAHR,

VERSN,

WBS_DES AS DESC1,

LTEXT AS B_DESC,

PMEHT,

PMG,

KSTAR,

KATYP,

GPREIS,

FWAER,

GPREIS1,

PMGC01 As P01,

PMGC02 As P02,

PMGC03 As P03,

PMGC04 As P04,

PMGC05 As P05,

PMGC06 As P06,

PMGC07 As P07,

PMGC08 As P08,

PMGC09 As P09,

PMGC10 As P10,

PMGC11 As P11,

PMGC12 As P12

FROM

.....QVD;

Table3

CrossTable(Month, Other , 15)

LOAD

POSKI AS B_WBS,

PSPID,

KALNR,

POSNR,

GJAHR,

VERSN,

WBS_DES AS DESC1,

LTEXT AS B_DESC,

PMEHT,

PMG,

KSTAR,

KATYP,

GPREIS,

FWAER,

GPREIS1,

PMGR01 As P01,

PMGR02 As P02,

PMGR03 As P03,

PMGR04 As P04,

PMGR05 As P05,

PMGR06 As P06,

PMGR07 As P07,

PMGR08 As P08,

PMGR09 As P09,

PMGR10 As P10,

PMGR11 As P11,

PMGR12 As P12

FROM

.....QVD;

Join(Table1)

LOAD * Resident Table2;

Join(Table1)

LOAD * Resident Table3

DROP Table Table2, Table3;

Re: Cross table help for two crosstable()

Anonymous — Tue, 17 Nov 2015 11:06:20 GMT

I doubt for same

Re: Cross table help for two crosstable()

Tue, 17 Nov 2015 12:05:31 GMT

Thanks

I figured out this way. As I dont want to concatenate.

/BUDGET_COST:

//CrossTable(Month, Qty, 10)

//LOAD PSPNR,

// POSID,

// POSKI,

// PSPID,

// KALNR,

// POSNR,

// GJAHR,

// VERSN,

// WBS_DES,

// LTEXT,

// PMG001 AS 04,

// PMG002 AS 05,

// PMG003 AS 06,

// PMG004 AS 07,

// PMG005 AS 08,

// PMG006 AS 09,

// PMG007 AS 10,

// PMG008 AS 11,

// PMG009 AS 12,

// PMG010 AS 01,

// PMG011 AS 02,

// PMG012 AS 03

//FROM

//(qvd) WHERE VERSN like 'A*' AND PSPNR<>'00000000' ;

//BUDGET_COST1:

//CrossTable(Month, Amount, 10)

//LOAD PSPNR,

// POSID,

// POSKI,

// PSPID,

// KALNR,

// POSNR,

// GJAHR,

// VERSN,

// WBS_DES,

// LTEXT,

// PMGC01 AS 04,

// PMGC02 AS 05,

// PMGC03 AS 06,

// PMGC04 AS 07,

// PMGC05 AS 08,

// PMGC06 AS 09,

// PMGC07 AS 10,

// PMGC08 AS 11,

// PMGC09 AS 12,

// PMGC10 AS 01,

// PMGC11 AS 02,

// PMGC12 AS 03

//FROM

//(qvd) WHERE VERSN like 'A*' AND PSPNR<>'00000000' ;

//NoConcatenate

//F_B_COST:

//Load *

//Resident BUDGET_COST;

//inner join

//load *

//Resident BUDGET_COST1;

//drop table BUDGET_COST1;

//STORE F_B_COST INTO E:\ASHIF\F_B_COST.QVD;

//DROP Table F_B_COST;