topic Help with logic in QlikView

Help with logic

Thu, 05 Apr 2012 14:48:15 GMT

I am trying to display a sum of quantity sold within a set of ranges, and I am not getting the results that I need.

Here are the ranges:

0-11

12-19

20-29

30-39

40-79

80+

My primary data is as follows:

'CAGE_Region',

[CAGE District] as 'CAGE_District',

[CAGE Territory] as 'CAGE_Territory',

MakeDate(Left([GL Date],4),Mid([GL Date],5,2),Right([GL Date],2)) as 'Calendar_Date',

[Location Number] as 'Location_Number',

[Primary Brand] as 'Brand',

[Invoice Extended Amount] as 'Net_Sales',

[Carton Equivalent] as 'QVOF'

I am using the following to classify the ranges within the load script, but I am not getting the unique count by location since the load script is using the calendar date.

Carton_Bucket:

LOAD

[CAGE Region] as 'CAGE_Region',

[CAGE District] as 'CAGE_District',

[CAGE Territory] as 'CAGE_Territory',

MakeDate(Left([GL Date],4),Mid([GL Date],5,2),Right([GL Date],2)) as 'Calendar_Date',

[Primary Brand] as 'Brand',

[Location Number] as 'Location_Number',

If(([Carton Equivalent]<12),'<12',If(([Carton Equivalent]>=12 and [Carton Equivalent]<20),'12-19',

If(([Carton Equivalent]>=20 and [Carton Equivalent]<30),'20-29',If(([Carton Equivalent]>=30 and [Carton Equivalent]<40),'30-39',

If(([Carton Equivalent]>=40 and [Carton Equivalent]<80),'40-79','80+'))))) as 'Carton_Bucket'

FROM

(txt, unicode, embedded labels, delimiter is '\t', msq);

Calendar Date Location_Number Carton Equivalent

3/1/12 123 2

3/2/12 123 4

3/3/12 123 20

The load script is generating the following for location 123 (6 cartons in the 0-11 range and 20 cartons in the 20-29 range)

What I am looking for is 26 cartons total in the 20-29 range (total cartons for each location regardless of day), but I cannot figure out how to get this work correctly.

Any ideas on what I am doing wrong?

Help with logic

CELAMBARASAN — Thu, 05 Apr 2012 15:21:04 GMT

Hi,

Try this

Carton:

LOAD

[CAGE Region] as 'CAGE_Region',

[CAGE District] as 'CAGE_District',

[CAGE Territory] as 'CAGE_Territory',

MakeDate(Left([GL Date],4),Mid([GL Date],5,2),Right([GL Date],2)) as 'Calendar_Date',

[Primary Brand] as 'Brand',

[Location Number] as 'Location_Number'

FROM

(txt, unicode, embedded labels, delimiter is '\t', msq);

Carton_Bucket:

Load

Location_Number,

If((Sum([Carton Equivalent])<12),'<12',If((Sum([Carton Equivalent])>=12 and Sum([Carton Equivalent])<20),'12-19',

If((Sum([Carton Equivalent])>=20 and Sum([Carton Equivalent])<30),'20-29',If((Sum([Carton Equivalent])>=30 and Sum([Carton Equivalent])<40),'30-39',

If((Sum([Carton Equivalent])>=40 and Sum([Carton Equivalent])<80),'40-79','80+'))))) as 'Carton_Bucket'

Resident

Carton

Group by Location_Number;

Celambarasan

Help with logic

jagannalla — Thu, 05 Apr 2012 15:27:48 GMT

Try like this it may helps u..

Temp:

LOAD

RowNo() as CartonEquivalent

AutoGenerate 100;

ResTemp:

LOAD CartonEquivalent as M1, '0-11' as Range

Resident Temp

Where CartonEquivalent <12;

Concatenate

LOAD CartonEquivalent as M1, '12-19' as Range

Resident Temp

Where CartonEquivalent <20

and not Exists(M1,CartonEquivalent)

;

Concatenate

LOAD CartonEquivalent as M1, '20-29' as Range

Resident Temp

Where CartonEquivalent <30

and not Exists(M1,CartonEquivalent)

;

Concatenate

LOAD CartonEquivalent as M1, '30-39' as Range

Resident Temp

Where CartonEquivalent <40

and not Exists(M1,CartonEquivalent)

;

Concatenate

LOAD CartonEquivalent as M1, '40-79' as Range

Resident Temp

Where CartonEquivalent <80

and not Exists(M1,CartonEquivalent)

;

Concatenate

LOAD CartonEquivalent as M1, '80+' as Range

Resident Temp

Where CartonEquivalent >=80;

Map1:

Mapping

LOAD M1,Range

Resident ResTemp;

DROP Table ResTemp;

Temp2:

LOAD ApplyMap ('Map1', CartonEquivalent ) as Range1,CartonEquivalent

Resident Temp;

DROP Table Temp;

Help with logic

jagannalla — Thu, 05 Apr 2012 15:34:29 GMT

If we convert according to your code. it will look like this

Temp:

LOAD

CartonEquivalent

FROM

(txt, unicode, embedded labels, delimiter is '\t', msq);

ResTemp:

LOAD CartonEquivalent as M1, '0-11' as R1

Resident Temp

Where CartonEquivalent <12;

Concatenate

LOAD CartonEquivalent as M1, '12-19' as R1

Resident Temp

Where CartonEquivalent <20

and not Exists(M1,CartonEquivalent)

;

Concatenate

LOAD CartonEquivalent as M1, '20-29' as R1

Resident Temp

Where CartonEquivalent <30

and not Exists(M1,CartonEquivalent)

;

Concatenate

LOAD CartonEquivalent as M1, '30-39' as R1

Resident Temp

Where CartonEquivalent <40

and not Exists(M1,CartonEquivalent)

;

Concatenate

LOAD CartonEquivalent as M1, '40-79' as R1

Resident Temp

Where CartonEquivalent <80

and not Exists(M1,CartonEquivalent)

;

Concatenate

LOAD CartonEquivalent as M1, '80+' as R1

Resident Temp

Where CartonEquivalent >=80;

Map1:

Mapping

LOAD M1,R1

Resident ResTemp;

DROP Table ResTemp,Temp;

MainTable:

LOAD

[CAGE Region] as 'CAGE_Region',

[CAGE District] as 'CAGE_District',

[CAGE Territory] as 'CAGE_Territory',

MakeDate(Left([GL Date],4),Mid([GL Date],5,2),Right([GL Date],2)) as 'Calendar_Date',

[Primary Brand] as 'Brand',

[Location Number] as 'Location_Number',

ApplyMap ('Map1', CartonEquivalent ) as Range

FROM

(txt, unicode, embedded labels, delimiter is '\t', msq);

Help with logic

Thu, 05 Apr 2012 15:41:24 GMT

I got the following Script Error?

Table not found

Carton_Bucket:

LOAD

Location_Number,

If((Sum([Carton Equivalent])<12),'<12',If((Sum([Carton Equivalent])>=12 and Sum([Carton Equivalent])<20),'12-19',

If((Sum([Carton Equivalent])>=20 and Sum([Carton Equivalent])<30),'20-29',If((Sum([Carton Equivalent])>=30 and Sum([Carton Equivalent])<40),'30-39',

If((Sum([Carton Equivalent])>=40 and Sum([Carton Equivalent])<80),'40-79','80+'))))) as 'Carton_Bucket'

Resident

Carton

Group by Location_Number

Re: Help with logic

Thu, 05 Apr 2012 15:42:15 GMT

I got the following Script Error?

Table not found

Carton_Bucket:

LOAD

Location_Number,

If((Sum([Carton Equivalent])<12),'<12',If((Sum([Carton Equivalent])>=12 and Sum([Carton Equivalent])<20),'12-19',

If((Sum([Carton Equivalent])>=20 and Sum([Carton Equivalent])<30),'20-29',If((Sum([Carton Equivalent])>=30 and Sum([Carton Equivalent])<40),'30-39',

If((Sum([Carton Equivalent])>=40 and Sum([Carton Equivalent])<80),'40-79','80+'))))) as 'Carton_Bucket'

Resident

Carton

Group by Location_Number

Help with logic

CELAMBARASAN — Thu, 05 Apr 2012 15:58:04 GMT

Hi,

Have you replaced the Carton_Bucket with my script(Carton and Carton_Bucket tables)

Celambarasan

Re: Help with logic

Thu, 05 Apr 2012 16:05:29 GMT

yes, I did and I got that error message.

I was thinking, can I accomplish this in a calculated dimension? I would like to user to be able to click through the dimensions (brand, region, etc) and view the carton bucket in the graph. Does that give more flexibility than doing it through the load script?

Re: Help with logic

Thu, 05 Apr 2012 16:05:43 GMT

yes, I did and I got that error message.

Help with logic

jagannalla — Fri, 06 Apr 2012 04:24:50 GMT

Hello,

you should make group by[Carton Equivalent] and ur missing this field in main table.

Carton:

LOAD

[CAGE Region] as 'CAGE_Region',

[CAGE District] as 'CAGE_District',

[CAGE Territory] as 'CAGE_Territory',

MakeDate(Left([GL Date],4),Mid([GL Date],5,2),Right([GL Date],2)) as 'Calendar_Date',

[Primary Brand] as 'Brand',

[Location Number] as 'Location_Number',

[Carton Equivalent]

FROM

(txt, unicode, embedded labels, delimiter is '\t', msq);

Carton_Bucket:

Load

[Carton Equivalent],

If((Sum([Carton Equivalent])<12),'<12',If((Sum([Carton Equivalent])>=12 and Sum([Carton Equivalent])<20),'12-19',

If((Sum([Carton Equivalent])>=20 and Sum([Carton Equivalent])<30),'20-29',If((Sum([Carton Equivalent])>=30 and Sum([Carton Equivalent])<40),'30-39',

If((Sum([Carton Equivalent])>=40 and Sum([Carton Equivalent])<80),'40-79','80+'))))) as 'Carton_Bucket'

Resident

Carton

Group by [Carton Equivalent];

hope it helps u.