Count of Weeks

[qlik4asif]

Hey Guys,

Can some one help in writing the set analysis for the below scenario.

Some Sample Data is as below

T1:
Load *
Inline [
Material,Stock
P001,1000
P002,850
P003,900
P004,750
];

T2:
Load *
Inline [
Material,Date,Week,Orders
P001,23/12/2019,52,500
P002,23/12/2019,52,400
P003,23/12/2019,52,550
P004,23/12/2019,52,550
P001,26/12/2019,52,200
P003,26/12/2019,52,100
P001,02/01/2020,1,200
P002,02/01/2020,1,100
P001,07/01/2020,2,300
P002,07/01/2020,2,100
P003,07/01/2020,2,300
P002,14/01/2020,2,100
P004,14/01/2020,2,500
];

With the above Available Stock for how many weeks we can be able to supply the orders.(I guess some we need to use some Rangesum() function and compare with the stock)

For POO1 - 2 Weeks, (Stock 1000, Orders - (500+200+200) 2 weeks, we cant supply to last order(07/01/2020) because we don't have enough stock)

POO2 - 3 Weeks,

POO3 - 2 Weeks,

POO4 - 1 Week

I want No of weeks we can supply with available stock

Thanks in Advance

[Taoufiq_Zarra]

Hi,

if I follow the same logic P004 should be 3 weeks and not 1 week.

The qlikview file is attached.
you will get the following results

or here's the code,
the code can be optimized, but I prefer to keep it long to show the logic.

T1:
Load *
Inline [
Material,Stock
P001,1000
P002,850
P003,900
P004,750
];
left join
T2:
Load *
Inline [
Material,Date,Week,Orders
P001,23/12/2019,52,500
P002,23/12/2019,52,400
P003,23/12/2019,52,550
P004,23/12/2019,52,550
P001,26/12/2019,52,200
P003,26/12/2019,52,100
P001,02/01/2020,1,200
P002,02/01/2020,1,100
P001,07/01/2020,2,300
P002,07/01/2020,2,100
P003,07/01/2020,2,300
P002,14/01/2020,2,100
P004,14/01/2020,2,500
];


temps1:
NoConcatenate
load Material,
sum(Orders) as rest,
Date,
Rangesum(sum(Orders),if(peek('Material')=Previous(Material),peek('Sum_Orders'),0)) as Sum_Orders
Resident T1
group by Material,Date
Order by Material,Date
;

temps2:
NoConcatenate
load * Resident temps1;
Left join
LOAD * Resident T1;

Final:
NoConcatenate
load *,if((Stock-(Sum_Orders))<0,(Date),0) as flag
Resident temps2
Order by Material,Date;

DROP table T1,temps1,temps2;

[qlik4asif]

Thanks for the reply.

Can we do it in front end?

because i don't want to change the data model in background, everything is coming correct now.

[Taoufiq_Zarra]

personally I prefer it in Load Script.

You can use QVD if you wan't change your model

[Kushal_Chawda]

It will be difficult to do it on front end. I would say probably too complex . You can do something like this in script

T1:
Load *
Inline [
Material,Date,Week,Orders
P001,23/12/2019,52,500
P002,23/12/2019,52,400
P003,23/12/2019,52,550
P004,23/12/2019,52,550
P001,26/12/2019,52,200
P003,26/12/2019,52,100
P001,02/01/2020,1,200
P002,02/01/2020,1,100
P001,07/01/2020,2,300
P002,07/01/2020,2,100
P003,07/01/2020,2,300
P002,14/01/2020,2,100
P004,14/01/2020,2,500
];

Left Join(T1)
Load *
Inline [
Material,Stock
P001,1000
P002,850
P003,900
P004,750
];

T2:
Load Material,
Date,
Week,
Orders,
Stock,
if(Material=Previous(Material),rangesum(peek(AccumOrders),Orders),Orders) as AccumOrders
Resident T1
Order by Material,Date;

Drop Table T1;

T3:
Load Material,
Count(DISTINCT Week)&if(Count(DISTINCT Week)=1,' Week',' Weeks') as No.Weeks
Resident T2
Where AccumOrders<=Stock
Group by Material;