5 Replies Latest reply: May 18, 2012 7:01 AM by icehorde RSS

    Creating criteria for count of records ?


      Hello qlik community,


      what i am trying to achieve in my current document is to calcluate the transport cost per kg imported goods. The problem is that the accounting dates for a delivery and transport doesn't match. For examle ( as you will see in the attached file ) i could have 4 deliveries but 3 accounted transports so in order to calculate cost/kg i have to use the quantity of 3 deliveries.


      I have two tables, one for deliveries and one for transport. The keys are Code, Date and Month.


      I have tried to add new field RowNo and sum transport/kg for matching rows. I sort the two tables by date and code and things work untill transport doens't match deliveries because the two tables have different number of rows. Apparently delivery table has the max number of rows so my idea is to create the transport table with the same number of rows, i.e 4 deliveries and 4 transports with 1 null row but with rowno 4 as in the example .


      Thank you



        • Creating criteria for count of records ?

          Sorry, I don't get what you want.


          I see only one possible key here, which is Code. Dates seem to possibly differ between related deliveries and transports? Quantitiy is your measure.


          So what are you trying your achieve? Could you give the expected outcome for your posted sample data and also explain how you would calculate it manually? Then probably someone can try to create a data model and expressions out of that.

            • Creating criteria for count of records ?

              Sorry, i was not quite detailed.


              Yes, dates differ between related deliveries and transports. The measure unit for Quantity is kolograms and for Credit is local currency.




              Table 2

              Table 1 is deliveries , Table 2 is transport. As you can see there are 4 deliveries and only 3 transports for them. Dates differ for relevant deliveries and transport wich means that the delivery from 17.04.12 of 200 kg costs 40 units local currency from 19.04.12 (first delivery for first transport and so on, no matter the date).

              If i would do it manually i would sum credit(40+42+12)/ sum quantity (200+210+60).