Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Not applicable

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

IceHorde

1 Solution

Accepted Solutions
MVP
MVP

Re: Creating criteria for count of records ?

I tried to follow your idea with matching lines by creating a kind of linenumber per code, using autonumber. This ID is always starting from 1 per Code, for both deliveries and transports.

So, in your chart, you can use advanced aggregation to sum up the delivery quantities only up to the linenumber that matches the max transport linenumber.

Please see attached.

5 Replies
MVP
MVP

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.

Not applicable

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.

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

cesaraccardi
Valued Contributor

Re: Creating criteria for count of records ?

Hi, i've attached a sugestion... I made a single table with concatenation and the  interface with set analysis and a variable to select the date for analysis. Please take a look.


Regards,
Cesar

MVP
MVP

Re: Creating criteria for count of records ?

I tried to follow your idea with matching lines by creating a kind of linenumber per code, using autonumber. This ID is always starting from 1 per Code, for both deliveries and transports.

So, in your chart, you can use advanced aggregation to sum up the delivery quantities only up to the linenumber that matches the max transport linenumber.

Please see attached.

Not applicable

Re: Creating criteria for count of records ?

This works pretty good!

Thank you!