Logic Calculation - Match & Not Match Cases based on Condition

[erric3210]

Hi Community,

I've 2 tables and based on Cars matching and not matching between both tables,

I've to calculate the Count. I've this below requirement:

Primary Key b/w both table is Car Model.

Table 1:

Car Model	Car Model 1	Company
Fortuner	Fortuner	Toyota
Land Cruiser	Land Cruiser	Toyota
520d	520d	BMW
X5	X5	BMW
Q7	Q7	Audi
GTR	GTR	Nissan

Table 2:

Car Model	Car Model 2
Land Cruiser	Land Cruiser
Q7	Q7
GTR	GTR

Logic Requirement:

Regards,

Eric

[maxgro · Accepted solution]

I added some rows for test

Table1:
Load * inline [
CarModel, CarModel1, Company
Fortuner, Fortuner, Toyota
Land Cruiser, Land Cruiser, Toyota
520d, 520d, BMW
X5, X5, BMW
Q7, Q7, Audi
GTR, GTR, Nissan
Mondeo, Mondeo, Ford
Fiesta, Fiesta, Ford
Golf, Golf, Volkswagen
Megane, Megane, Renault
Espace, Espace, Ranault
];

Table2:
load * inline[
CarModel, CarModel2
Land Cruiser, Land Cruiser
Q7, Q7
GTR, GTR
Mondeo, Mondeo
];

Expressions used in the 2nd chart

// count the car model where model1 = model2

count({$ <CarModel={"=(CarModel1=CarModel2)"}>} CarModel)

concat({$ <CarModel={"=(CarModel1=CarModel2)"}>} CarModel, chr(10))

// count the car model where model1 <> model2 and the company isn't a company of the car model where model 1 = model2

count({$ <CarModel={"=(CarModel1<>CarModel2)"}, Company=E({$ <CarModel={"=(CarModel1=CarModel2)"}>} Company)>} CarModel)

concat({$ <CarModel={"=(CarModel1<>CarModel2)"}, Company=E({$ <CarModel={"=(CarModel1=CarModel2)"}>} Company)>} CarModel, chr(10))

[erric3210]

Hi @maxgro  Sir,

Thank you so much for your reply. This is exactly what I need.

Your solution worked perfectly for my case.

It was indeed a great learning for me.

Many Thanks.

Regards,
Eric