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

    Creating criteria for count of records ?

    icehorde

      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

       

      IceHorde

        • Creating criteria for count of records ?
          swuehl

          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 ?
              icehorde

              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.

               

              Table1
              DateCodeTextQuantity
              17.04.1212345delivery200
              19.04.1212345delivery210
              23.04.1212345delivery60
              29.04.1212345delivery100

               

              Table 2
              DateCodeTextCredit
              19.04.1212345transport40
              24.04.1212345transport42
              25.04.1212345transport12

              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).

               

              IceHorde