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