topic Re: Calculation of count in script level in QlikView

Calculation of count in script level

bushpalaamarnat — Thu, 17 May 2018 08:29:12 GMT

Hi all,

Kindly help, i am trying to get count of the data at script level only. Following is my script, with out creating an other table of resident i wan to get count of OOS, Low Stock, Intolerance, Overstock, NoDemand. Could any help, please.

Weekly_ICD:

NoConcatenate

load

if(N_DFC = 0 and [Inventory Volume] < 0,'OOS',

if(N_DFC < [Min DFC] and [Inventory Volume] > 0, 'Low Stock',

if(N_DFC >= [Min DFC] and N_DFC <= [Max DFC], 'Intolerance',

if(N_DFC > [Max DFC], 'Overstock',

if(N_DFC = 0 and [Inventory Volume] = 0, 'NoDemand'))))) as N_Inventory_Position,

([Inventory Volume]-( [Inventory Volume]/N_DFC))* ([Max DFC]/N_DFC) as M_overstock,

(([Inventory Volume]-( [Inventory Volume]/N_DFC))* ([Max DFC]/N_DFC))*3.3 as Delta_overstock

Resident Weekly_ICR;

Drop Tables Weekly_ICR;

Re: Calculation of count in script level

jayshrinipurte — Thu, 17 May 2018 08:49:18 GMT

Dear Neelima,

May be it will help you,

If(N_DFC = 0 and [Inventory Volume] < 0, Count(Field_Name)) as OOS,

if(N_DFC < [Min DFC] and [Inventory Volume] > 0, Count(FieldName)) as LowStock,

if(N_DFC >= [Min DFC] and N_DFC <= [Max DFC],Count(FieldName)) as Intolerance,

if(N_DFC > [Max DFC], Count(FieldName)) as Overstock,

if(N_DFC = 0 and [Inventory Volume] = 0, Count(FieldName)) as NoDemand,

Regards,

Jayshri

Re: Calculation of count in script level

bushpalaamarnat — Thu, 17 May 2018 10:31:20 GMT

thank you for your response, it did not work gives me invalid expression.

is there some thing else, you think should be done?

Re: Calculation of count in script level

shiveshsingh — Thu, 17 May 2018 10:40:10 GMT

You are creating OOS, Low Stock, Intolerance, Overstock, NoDemand in one table Weekly_ICD and in that table only you need the count?

These are N_Inventory_Position and you need count for that, right?

Re: Calculation of count in script level

bushpalaamarnat — Thu, 17 May 2018 14:35:17 GMT

Yes Sir. you got it.

is it some thing like group by we should be doing? or how to write in the script.

kindly advise.

Re: Calculation of count in script level

vishsaggi — Thu, 17 May 2018 15:27:36 GMT

You need to use Group By clause for this script to get the counts. However, your script has Load * what will be your Group By field list here? Can you share some sample data to work on? You should do a preceding Load to get the count of N_Inventory_Position by their values, but you might also add other dimensions here in your Group By as you are using NoConcatenate for this table? Doable but may be not in single table I believe?

Re: Calculation of count in script level

vishsaggi — Thu, 17 May 2018 15:40:51 GMT

May be if you can accommodate using Left join you can try like

Weekly_ICD:

NoConcatenate

Final:

load

if(N_DFC = 0 and [Inventory Volume] < 0,'OOS',

if(N_DFC < [Min DFC] and [Inventory Volume] > 0, 'Low Stock',

if(N_DFC >= [Min DFC] and N_DFC <= [Max DFC], 'Intolerance',

if(N_DFC > [Max DFC], 'Overstock',

if(N_DFC = 0 and [Inventory Volume] = 0, 'NoDemand'))))) as N_Inventory_Position,

([Inventory Volume]-( [Inventory Volume]/N_DFC))* ([Max DFC]/N_DFC) as M_overstock,

(([Inventory Volume]-( [Inventory Volume]/N_DFC))* ([Max DFC]/N_DFC))*3.3 as Delta_overstock

Resident Weekly_ICR

Group By Field1, Field2, Field3;

LEFT JOIN(Final)

load

N_Inventory_Position,

Count(N_Inventory_Position)

Resident Final

Group By N_Inventory_Position;

Re: Calculation of count in script level

shiveshsingh — Thu, 17 May 2018 16:41:24 GMT

You can find the count also but for that it can be done in another table which we can join with the first one.

What's the primary key in Final Table on which we can join the count(Field)