topic Re: Dropping, concatenating, crosstabling, summing etc. in QlikView

Dropping, concatenating, crosstabling, summing etc.

Wed, 15 Jul 2015 18:43:25 GMT

Hello all!

I'm having the hardest time figuring out why this isn't working. I'm trying to sum the objective sale quantities of all the product lines. However, it won't drop the table or add a cross table column and when I sum it, it sums incorrectly. Here is my script:

DealerObjectives:

CrossTable(temp, Data, 7)

LOAD [Account Number],

[Account Name],

Status,

[Contract Start Date],

[Contract End Date],

[Contract Record Type],

[Contract Number],

[Product 1 Q1],

[Product 1 Q2],

[Product 1 Q3],

[Product 1 Q4],

[Product 2 Q1],

[Product 2 Q2],

[Product 2 Q3],

[Product 2 Q4],

[Product 3 Q1],

[Product 3 Q2],

[Product 3 Q3],

[Product 3 Q4],

[Product 4 Q1],

[Product 4 Q3],

[Product 4 Q4],

[Product 5 Q1],

[Product 5 Q2],

[Product 5 Q3],

[Product 5 Q4],

[Product 6 Q1],

[Product 6 Q2],

[Product 6 Q3],

[Product 6 Q4],

[Product 7 Q1],

[Product 7 Q2],

[Product 7 Q3],

[Product 7 Q4],

[Product 8 Q1],

[Product 8 Q2],

[Product 8 Q3],

[Product 8 Q4],

[Product 9 Q1],

[Product 9 Q2],

[Product 9 Q3],

[Product 9 Q4]

FROM

(ooxml, embedded labels, table is report1432306847001);

DealerObjsFINAL: //2014

LOAD *,

right(temp, 2) as FiscalQuarter,

left(temp, len(temp)-3) as ProductLine,

'2014' as YearFlag

Resident dealerObj;

DealerObjsFINAL2:

LOAD *,

FiscalQuarter & '-' & YearFlag as FiscalQuarterYear

Resident DealerObjsFINAL;

drop field FiscalQuarter from DealerObjsFINAL2;

drop table dealerObj;

drop table DealerObjsFINAL;

PartsTotal:

load

[Account Number] as [PartsTotal_Acc.No],

[Parts Total objective]

FROM

(ooxml, embedded labels, header is 1 lines, table is report1);

NoConcatenate

Interest:

CrossTable(MonthDO, DealerObjsDatesData, 45)

LOAD [Account Number] as AccountNumber,

[Account Name],

Status,

'SFDCInt' as Flag,

[Contract Start Date],

[Contract End Date],

[Contract Record Type],

[Contract Number],

[Product 1 Q1],

[Product 1 Q2],

[Product 1 Q3],

[Product 1 Q4],

[Product 2 Q1],

[Product 2 Q2],

[Product 2 Q3],

[Product 2 Q4],

[Product 3 Q1],

[Product 3 Q2],

[Product 3 Q3],

[Product 3 Q4],

[Product 4 Q1],

[Product 4 Q3],

[Product 4 Q4],

[Product 5 Q1],

[Product 5 Q2],

[Product 5 Q3],

[Product 5 Q4],

[Product 6 Q1],

[Product 6 Q2],

[Product 6 Q3],

[Product 6 Q4],

[Product 7 Q1],

[Product 7 Q2],

[Product 7 Q3],

[Product 7 Q4],

[Product 8 Q1],

[Product 8 Q2],

[Product 8 Q3],

[Product 8 Q4],

[Product 9 Q1],

[Product 9 Q2],

[Product 9 Q3],

[Product 9 Q4],

[Product 10 Total Objective]

[41640],

[41671],

[41699],

[41730],

[41760],

[41791],

[41821],

[41852],

[41883],

[41913],

[41944],

[41974]

FROM

(ooxml, embedded labels, table is [Customer IDs with stuff and Int]);

NoConcatenate

InterestNorm:

LOAD *,

year(Date#(MonthDO, 'YYYY')) as MonthConv

Resident SFDCInt;

DROP Table Interest;

NoConcatenate

InterestFinal:

LOAD *,

date(MonthConv) as DATE

Resident InterestNorm;

DROP Table InterestNorm;

NoConcatenate

DealerObjsLASTLOAD:

LOAD * Resident DealerObjsFINAL2;

Concatenate

LOAD * Resident InterestFinal;

DROP Table DealerObjsFINAL2;

It keeps DealerObjsFINAL2 even though I clearly dropped it. Here is the summation that I tried:

=sum({<[temp]={'Product 1 Q1'}>}Data)+sum({<[temp]={'Product 1 Q2'}>}Data)+sum({<[temp]={'Product 1 Q3'}>}Data)+sum({<[temp]={'Product 1 Q4'}>}Data)+sum({<[temp]={'Product 2 Q1'}>}Data)+sum({<[temp]={'Product 2'}>}Data)+sum({<[temp]={'Product 2 Q3'}>}Data)+sum({<[temp]={'Product 2 Q4'}>}Data)+sum........etc.

Thanks so much for taking a look! Sorry to spam New to QlikView lately haha.

Re: Dropping, concatenating, crosstabling, summing etc.

sunny_talwar — Wed, 15 Jul 2015 18:57:02 GMT

First of all it is a good idea to use table names when you concatenate or join -> Concatenate (Table1), Join(Table2). It gets hard to know what is getting concatenated into what.

DealerObjectives:

CrossTable(temp, Data, 7)

drop table dealerObj;

Secondly, you don't have a table name called dealerObj (you have DealerObjectives), so might be a typo when you drop it.

Check if doing those things fix your issue, I will continue looking into the other issues.

Re: Dropping, concatenating, crosstabling, summing etc.

Mon, 20 Jul 2015 13:45:58 GMT

Sorry for the late response! That's a good tip on the naming things. As far as the dealerObjs, I just tried to change all the table names for privacy reasons and missed that one. So I don't think it's that. Good thought, though! Were you able to look into other issues? Thanks so much for your help!

Re: Dropping, concatenating, crosstabling, summing etc.

sunny_talwar — Mon, 20 Jul 2015 13:58:48 GMT

Try this:

DealerObjectives:

CrossTable(temp, Data, 7)

LOAD [Account Number],

[Account Name],

Status,

[Contract Start Date],

[Contract End Date],

[Contract Record Type],

[Contract Number],

[Product 1 Q1],

[Product 1 Q2],

[Product 1 Q3],

[Product 1 Q4],

[Product 2 Q1],

[Product 2 Q2],

[Product 2 Q3],

[Product 2 Q4],

[Product 3 Q1],

[Product 3 Q2],

[Product 3 Q3],

[Product 3 Q4],

[Product 4 Q1],

[Product 4 Q3],

[Product 4 Q4],

[Product 5 Q1],

[Product 5 Q2],

[Product 5 Q3],

[Product 5 Q4],

[Product 6 Q1],

[Product 6 Q2],

[Product 6 Q3],

[Product 6 Q4],

[Product 7 Q1],

[Product 7 Q2],

[Product 7 Q3],

[Product 7 Q4],

[Product 8 Q1],

[Product 8 Q2],

[Product 8 Q3],

[Product 8 Q4],

[Product 9 Q1],

[Product 9 Q2],

[Product 9 Q3],

[Product 9 Q4]

FROM

(ooxml, embedded labels, table is report1432306847001);

DealerObjsFINAL: //2014

LOAD *,

FiscalQuarter & '-' & YearFlag as FiscalQuarterYear;

LOAD *,

Right(temp, 2) as FiscalQuarter,

Left(temp, Len(temp)-3) as ProductLine,

2014 as YearFlag

Resident dealerObj;

DROP Field FiscalQuarter from DealerObjsFINAL;

DROP Table DealerObjectives;

PartsTotal:

LOAD [Account Number] as [PartsTotal_Acc.No],

[Parts Total objective]

FROM

(ooxml, embedded labels, header is 1 lines, table is report1);

Interest:

CrossTable(MonthDO, DealerObjsDatesData, 45)

LOAD [Account Number] as AccountNumber,

[Account Name],

Status,

'SFDCInt' as Flag,

[Contract Start Date],

[Contract End Date],

[Contract Record Type],

[Contract Number],

[Product 1 Q1],

[Product 1 Q2],

[Product 1 Q3],

[Product 1 Q4],

[Product 2 Q1],

[Product 2 Q2],

[Product 2 Q3],

[Product 2 Q4],

[Product 3 Q1],

[Product 3 Q2],

[Product 3 Q3],

[Product 3 Q4],

[Product 4 Q1],

[Product 4 Q3],

[Product 4 Q4],

[Product 5 Q1],

[Product 5 Q2],

[Product 5 Q3],

[Product 5 Q4],

[Product 6 Q1],

[Product 6 Q2],

[Product 6 Q3],

[Product 6 Q4],

[Product 7 Q1],

[Product 7 Q2],

[Product 7 Q3],

[Product 7 Q4],

[Product 8 Q1],

[Product 8 Q2],

[Product 8 Q3],

[Product 8 Q4],

[Product 9 Q1],

[Product 9 Q2],

[Product 9 Q3],

[Product 9 Q4],

[Product 10 Total Objective]

[41640],

[41671],

[41699],

[41730],

[41760],

[41791],

[41821],

[41852],

[41883],

[41913],

[41944],

[41974]

FROM

(ooxml, embedded labels, table is [Customer IDs with stuff and Int]);

InterestNorm:

LOAD *,

Year(Date(Num#(MonthDO, '##'))) as MonthConv,

Date(Num#(MonthDO, '##')) as DATE

Resident SFDCInt;

DROP Table Interest;

NoConcatenate

DealerObjsLASTLOAD:

LOAD *

Resident DealerObjsFINAL;

Concatenate

LOAD *

Resident InterestNorm;

DROP Table DealerObjsFINAL, InterestNorm;

Re: Dropping, concatenating, crosstabling, summing etc.

Tue, 21 Jul 2015 14:31:47 GMT

Thank you! You are so helpful!

Re: Dropping, concatenating, crosstabling, summing etc.

sunny_talwar — Tue, 21 Jul 2015 14:33:41 GMT

No problem at all

Always glad to help