Resident table, group, calculate and add new fields

[patricesalem]

Hello

I'm loading a file called ZSQ042 and, using this great forum and community (sincerely and truly amazed by the level of support), I have managed to create a first simple resident table to group some fields

[ZSQ042]

LOAD

country,

salesman,

product_group,

order_number,

date_order,

qty

from

ZSW042.xls;

[Aggregate_ZSQ042]

NoConcatenate

LOAD

country,

salesman,

product_group,

year(date_order),

sum(qty) as Total_Qty

group by country, salesman, product_group, year

Resident ZSQ042;

drop Table ZSQ042;

I would like to add a few columns to my Aggregate_ZSQ042 table that will be used later in Qlik view along with sales targets (defined by country, salesman and product_group) :

[total qty for Year N-2]

[total qty for Year N-1]

[total qty for Year N until enf of previous week]

[total qty for Year N-1 until end of previous week]

Year N being year(now())

I imagine that I have to use aggregate functions, where clauses...but I have no clue how to do it.

Do you have any idea how to proceed ?

Thanks in advance for your support

Patrice

[patricesalem]

Hello again

I have tried to aggregate the information into my new table but I'm really struggling since I'm not a SQL specialist...a small hand would be more than welcome...

thanks

[bismart]

[ZSQ042]

LOAD

country,

salesman,

product_group,

order_number,

date_order,

qty

from

ZSW042.xls;

[Aggregate_ZSQ042]

NoConcatenate

LOAD

country,

salesman,

product_group,

year(date_order),

sum(qty) as Total_Qty

Resident ZSQ042

group by country, salesman, product_group, year(date_order);

drop Table ZSQ042;

[patricesalem]

thanks, it helps me a bit.

is there a way to run the calculation not grouping on year(date_order) but adding 2 colums to my table

[total qty for Year N-2] : sum(qty) where year(date_order) = year(now())-2

[total qty for Year N-1] : sum(qty) where year (date_order) = year(now())-2

thanks

[bismart · Accepted solution]

[ZSQ042]

LOAD

country,

salesman,

product_group,

order_number,

date_order,

qty

from

ZSW042.xls;

[Aggregate_ZSQ042]

LOAD

country as Country, salesman as Salesman, product_group as [Product Group], year(Today()) as Period, sum(qty) as Qty

Resident ZSQ042

where year(date_order) = year(Today())

group by country, salesman, product_group, year(date_order);

LOAD

country as Country, salesman as Salesman, product_group as [Product Group], year(Today())-1 as Period, sum(qty) as Qty

Resident ZSQ042

where year(date_order) = year(Today())-1

group by country, salesman, product_group, year(date_order);

LOAD

country as Country, salesman as Salesman, product_group as [Product Group], year(Today())-2 as Period, sum(qty) as Qty

Resident ZSQ042

where year(date_order) = year(Today())-2

group by country, salesman, product_group, year(date_order);

drop Table ZSQ042;

[patricesalem]

hello

it did work !

thanks a lot for your help

patrice