Count difference

[Anonymous]

​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

[kaushiknsolanki]

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

[saimahasan]

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

[sunny_talwar]

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

[Anonymous]

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.

[johnw]

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

[Anonymous]

Hi ,

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

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

[Anonymous]

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

[johnw]

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;