topic Re: Count difference in QlikView

Count difference

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hi guys,

I need of your help.

I have three table, travel, option_A and option_B.

Travel is the main table, and it's connected by specific to the other two table.

The table travel have the field destination, while option_A have price_A and option_B have price_B.

So, I would know for every destination the number of situation in which price_A <> price_B.

I've tried

count(price_A <> price_B)

but it doesn't work.

How can I do this?

Thanks

Re: Count difference

kaushiknsolanki — Mon, 14 Nov 2016 09:49:55 GMT

Hi,. Why don't you link the option A and Option B table with main table using left join.

Once you join them, you will have both fields PriceA and PriceB in same table, then you can take resident of the table and use if statement like

If(PriceA<>PriceB,1,0) as flag

And then use this flag field to count like Sum(flag)

Regards,

Kaushik Solanki

Re: Count difference

saimahasan — Mon, 14 Nov 2016 12:57:32 GMT

can you please post sample data so that a much better suggestion/solution could be provided.

Re: Count difference

sunny_talwar — Mon, 14 Nov 2016 16:24:40 GMT

In case you don't want to join, you can always do like this in the front end, but I agree with kaushik.solanki‌ that if joining is an option, I would do this in the script

Sum(if(price_A <> price_B, 1, 0))

Re: Count difference

Anonymous — Mon, 14 Nov 2016 16:36:16 GMT

Thanks to all.

I've used Kaushik Solanki's solution.

My new question is, there is another way to do the same thing but without create other columns in the table?

I give you my old schema, maybe it can help to understand my questions.

Re: Count difference

johnw — Tue, 15 Nov 2016 00:36:05 GMT

If you don't want to make data model changes, try Sunny's solution.

But I guess I'd then ask why you didn't want to join the three tables together. Does it cause a problem?

Mind you, I wouldn't want to join them together either, but I wouldn't leave them as is either. I'd probably go with a rather different data structure allowing for 0 to many companies to provide prices for a given destination, and interpret your question as "how many destinations have more than one distinct price?"

Travel:
destination
description
target

Offers:
destination
company
price
time

And I think this expression would answer that question using that data model.

-sum(aggr(count(distinct price)>1,destination))

Re: Count difference

Anonymous — Tue, 15 Nov 2016 03:51:38 GMT

Hi ,

Following expression will work for your data model. Use this,

= -sum(aggr(count(distinct price)>1,destination))

Re: Count difference

Anonymous — Tue, 15 Nov 2016 15:07:15 GMT

There is no reason why I don't want join the tables.

I'm new about qlik world, and I'm exploring the alternative solution.

Thank to all for the help

Re: Count difference

johnw — Tue, 15 Nov 2016 16:56:57 GMT

OK, if you're open to data model changes, then building what I was suggesting might go something like this. You could alternatively get a crosstable load involved in here, but I'm going to skip that for now.

Travel:
LOAD
destination
,description
,target
FROM your raw travel data
;

Offers:
LOAD
destination
,id_offer_A as offer
FROM your raw travel data
;
INNER JOIN (Offers)
LOAD
id_offer_A as offer
,company_A as company
,price_A as price
,time_A as time
FROM your raw offer A data
;
OffersB:
LOAD
destination
,id_offer_B as offer
FROM your raw travel data
;
INNER JOIN (Offers)
LOAD
id_offer_B as offer
,company_B as company
,price_B as price
,time_B as time
FROM your raw offer B data
;
CONCATENATE (Offers)
LOAD *
RESIDENT OffersB
;
DROP TABLE OffersB;