topic Combine two fields om X-axe in QlikView

Combine two fields om X-axe

beunderf — Thu, 16 Jun 2011 10:24:39 GMT

Dear all,

I have the following question:

I have a table like this:

Transaction_ID_In	Transaction_ID_Out	Station_ID_In	Station_ID_Out
1	2	London	Liverpool
3	4	London	Liverpool
5	6	London	York
7	8	Liverpool	York
9	10	York	Amsterdam

If I want to show in a graph how many people check IN per station, I use Station_ID_In as Dimension and I Count(Transaction_ID_In) to count per station

If I want to show in a graph how many people check OUT per station, I use Station_ID_Out as Dimension and I Count(Transaction_ID_Out) to count per station.

But I want to show the Check ins AND check outs combined in ONE graph.

This means: Station names as dimension, and two expressions, one that counts the check ins and one that counts the check outs. Is this possible?

See attached file:

Re: Combine two fields om X-axe

matt_crowther — Thu, 16 Jun 2011 10:57:52 GMT

Hopefully solution attached.

Slight addition to the script and a change to the expressions.

I'm sure there are alternate methods to achieve this - I'll let you know if I come across any.

Hope that helps,

Matt - Visual Analytics Ltd

Re: Combine two fields om X-axe

IAMDV — Thu, 16 Jun 2011 11:02:20 GMT

Excellent Matt.

I am about to write the same. Recommending to amend the script and you gave a good example. Please share more techniques if any...

Thanks - DV

Re: Combine two fields om X-axe

Thu, 16 Jun 2011 11:25:18 GMT

Hi Frank and Matt,

I found some count mismatch in Matt's solution. I have a different approach to this to make the count correct. I have created a new table using the resident table as below and created a new chart which shows the correct counts.

NewData:
load Station_ID_In as Station, Transaction_ID_In as Transaction_ID, 'IN' as Transaction resident Data_1;
load Station_ID_Out as Station, Transaction_ID_Out as Transaction_ID, 'OUT' as Transaction resident Data_1;

PFA the modified QV file for the chart.

Best Regards,

Sajeevan

Combine two fields om X-axe

matt_crowther — Thu, 16 Jun 2011 11:36:03 GMT

That's very strange; in my version (v9 SR7) I get correct counts:

But when I open your posted example the same chart does indeed show the incorrect counts:

Strange indeed.

All the best,

Matt - Visual Analytics Ltd

Re: Combine two fields om X-axe

beunderf — Thu, 16 Jun 2011 11:41:51 GMT

Hi Matt, this looks nice, If I add 'distinct' while creating the stations, it gives the correct values.

So this is usefull, but is there a way of doing this, without creating an extra table. Is it possible to use some kind of Set analysis?

Frank

Combine two fields om X-axe

Thu, 16 Jun 2011 11:44:10 GMT

Hi Matt,

Thanks for your feedback. I am having V9 SR2 and getting the wrong results - which means it is time for me to upgrade.

Best Regards,

Sajeevan

Re: Combine two fields om X-axe

beunderf — Thu, 16 Jun 2011 11:48:42 GMT

Hi Sajeevan,

Thanks for your answer. The only problem is that your solution doubles the number of rows. If I tell you that in my real table every row has 30 fields and the the table contains over 90.000.000 rows, this is not very useful.

Do you have an idea creating the same graph without doubling the rows. Maybe with some kind of Set Analysis?

Hope you can help me!

Best regards,

Frank

Combine two fields om X-axe

Thu, 16 Jun 2011 12:18:52 GMT

Hi Frank,

Thanks for the feedback. If this is the case then what Matt suggested is the best approach but just load only the distinct station names in a different table like below then the counts will become correct if you are using lower version of the QlikView.

Station:

load distinct Station_ID_In as Station resident Data_1;

load distinct Station_ID_Out as Station resident Data_1;

Set analysis requires a searching value which in your case is the station name or the IN/OUT activity and are in two different fields which will be quite complex. I will let you know the solution if I am successful.

Thanks,

Sajeevan