2 Replies Latest reply: Aug 22, 2011 9:18 AM by brownoae RSS

    Issue with script creating duplicate records

      Please see attached QVW.

       

      I am pulling shipment data into my app, along with all of its associated data.  Based on the key (entry no) I am needing to sum 4 separate quantity values (order qty, receipt qty, shipped qty, and entry amt).  Only entry amt is grouping together correctly in my list box, while shipment qty, order qty, and receipt qty are not.  Because of this, my entry no associated to all of the totals is listing 494 times.  When I roll these totals into my pivot, it is taking the proper totals and multiplying them by 494.  The individual qty's are listing correctly, but not summing correctly.  I am new to qlikview development and not sure if I have an error in my script or if I am displaying the data incorrectly.

       

      Any help or suggestions would be appreciated

        • Issue with script creating duplicate records

          Hello,

          as far I can see is your problem because you are grouping in different ways.

           

          Entry Amt:

          . . .

              SUM([Entry Amt]) as [Entry Amt]

          RESIDENT EntryPrep

          GROUP BY [Entry No];

           

          BUT Shipped Qty:

          . . .

              sum([Shipped Qty]) as [Shipped Qty]

          RESIDENT ShipmentTemp

          GROUP BY [Entry No],[Shipment Date],PO,[PO Row],[Trans Mode],[Country of Origin];

           

          The (correct!) grouping results can be seen in the QV-Listboxes of these two fields. If you want to see one uniqe value (494 times) in the "Shipped Qty" for the current selection analog to "Entry Amt" use same grouping.

           

          Nevertheless the summing up in your pivot should be correct as your grouping is different but the count([Shipped Qty] ) is also 494. So it seems to me, that you do not "loose" records during your joins. But the only one who knows it exactly is you.

          I used statistic boxes (also for "receipt qty" and "order qty"). There you can see next point: that you have three missing values for the "receipt qty" and "order qty". May be there you should take care too.

           

          HtH

          Roland

           

          P.S.:

          For a QV-newbie an interessting load-script ;-)