Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2023, a live, in-person thrill ride. Save $300 before February 6: REGISTER NOW!
cancel
Showing results for 
Search instead for 
Did you mean: 
bushpalaamarnat
Creator
Creator

Count function and data problem

In qlikview, i have created two table, one table which showing the details of out of stock(OOS) where as in the other table, we have summary. the problem is in the details table, there are 6 OOS where as in summary table, it is showing 9 as a count.

This is the formula used to count OOS. Sum(if(Inventory_Position='OOS',1,0))

Could you help. attaching a word document with details.

this is the back end script:

//NULLASVALUE [Safety Days' Supply];

//NULLASVALUE [Demand Volume (Eaches)];

//NULLASVALUE  DFC;

//SET NullValue ='0' ;

WeeklyCorridor:

Left join

LOAD

Week,

[Supplier Loc] & [Customer Loc] &  Material as %key,

[Customer Loc] &  Material as %key2,

     Material,

     [Material Description],

     [Material Type],

     Category,

[Inventory Volume],

     [Demand Volume (Eaches)],

     DFC,

     Brand,

     Variant,

     Lifecycle,

     [Customer Loc],

     [Customer Location Description],

     [Customer Zone],

     [Supplier Loc],

     [Supplier Gov BU],

     [Corridor Status],

     [Min DFC],

     [Max DFC],

      if([Safety Days Supply]='-',0,[Safety Days Supply]) as [Safety Days Supply],

   

     //Creation of rules

    IF(((num([Min DFC])+ num([Max DFC]))<> 0) and num([Min DFC]) >= num([Max DFC]),'Reason- Min >= Max')                  as Rule1,

    IF((num([Safety Days Supply]) +  num([Max DFC])) <> 0 and num([Safety Days Supply]) >= num([Max DFC] ), 'Reason-SSD>Max')      as Rule2,

    IF(((num([Min DFC])+ num([Max DFC]))=0 AND num([Safety Days Supply]) <> 0),'Reason-SSD has value when Min & Max is zero')        as Rule3,

    If((num([Min DFC]) + num([Max DFC]))=0 and Lifecycle<>'Material Run Down','Reason-Min Max not set')      as Rule4,

    IF( Lifecycle='Material Run Down' and (num([Min DFC])+ num([Max DFC]))<>0, 'Reason-RunDown SKU Min & Max > Zero')      as Rule5,

    IF((num([Safety Days Supply])+num([Min DFC]))<>0 and num([Safety Days Supply])<= num([Min DFC]),'Reason-SSD <= Min') as Rule6,

    IF([Safety Days Supply]='-' and num([Max DFC])>0, 'Reason-SSD not set')      as Rule7,

    IF([Safety Days Supply]='-' and (num([Min DFC])+ num([Max DFC]))=0, 'Min & Max = Zero')              as Rule8,

    if([Min DFC]='-' and [Max DFC]='-','Reason-Min Max not set')      as Rule9,

 

  // Inventory Position calcualtions as follows:

   

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

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

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

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

          if(DFC = 0 and [Inventory Volume] = 0, 'NoDemand')))))      as Inventory_Position,

 

  //Creation of action items  based on rules created for planners.

    IF(((num([Min DFC])+ num([Max DFC]))<> 0) and num([Min DFC]) >= num([Max DFC]),'Reason- Min >= Max',

    IF((num([Safety Days Supply]) +  num([Max DFC])) <> 0 and num([Safety Days Supply]) >= num([Max DFC] ), 'Reason-SSD>Max',

    IF((num([Min DFC])=0 and num([Max DFC])=0 AND num([Safety Days Supply]) > 0),'Reason-SSD has value when Min & Max is zero',

    If((num([Min DFC]) + num([Max DFC]))=0 and Lifecycle<>'Material Run Down','Reason-Min Max not set',

    IF( Lifecycle='Material Run Down' and (num([Min DFC])+ num([Max DFC]))<>0 , 'Reason-RunDown SKU Min Max > Zero',

    IF((num([Safety Days Supply])+num([Min DFC]))<>0 and num([Safety Days Supply])<= num([Min DFC]),'Reason-SSD <= Min',

    IF([Safety Days Supply]='-' and num([Max DFC])>0, 'Reason-SSD Notdefined',

    IF([Safety Days Supply]='-' and (num([Min DFC])+ num([Max DFC]))=0, 'Min&Max = Zero and SSD is blank',

    if([Min DFC]='-' and [Max DFC]='-','Reason-Min Max not set',

    if([Min DFC]='-', 'Min Not Set',

    if([Max DFC]='-', 'Max Not Set', 'All Set'))))))))))) AS ActionItem

FROM

Load.qvd]

(qvd);

0 Replies