Calculation of count in script level

[bushpalaamarnat]

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;

[jayshrinipurte]

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

[bushpalaamarnat]

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

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

[shiveshsingh]

Hi

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?

[bushpalaamarnat]

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.

[vishsaggi]

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?

[vishsaggi]

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;

[shiveshsingh]

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)