topic Re: Need a set analysis expression in QlikView

Need a set analysis expression

woshua5550 — Sat, 16 Nov 2024 02:14:02 GMT

Hi everybody,

I have 2 tables like this

Table1

Relationship

All I want to do is to create a straight table with Model and ReportMonth dimension, and count the number of VIN1/VIN2 for each Model when WSMonth = ReportMonth

expect result :

Model	ReportMonth	Count VIN1	Count VIN2
A	201901	1	0
B	201901	1	1
B	201902	1	1

what should my expression looks like ?

Thanks !

Dave

Re: Need a set analysis expression

Arthur_Fong — Mon, 14 Oct 2019 06:35:52 GMT

Try joining these 2 tables together:

Remove the set analysis at the second expression.

Refer script as below:

LOAD * Inline [
Model,ReportMonth,VIN1
A,201901,V1
B,201901,V2
B,201902,V3
];
JOIN
LOAD Model,WSMonth AS ReportMonth,VIN2 Inline [
Model,WSMonth,VIN2
B,201901,V4
B,201902,V5
];

Thanks and regards,

Arthur Fong

Re: Need a set analysis expression

woshua5550 — Mon, 14 Oct 2019 09:31:23 GMT

Hi Arthur

Thanks, but I'm afraid we can't do the join because the 2 tables stands for different business meanings, you can consider that table1 is stock information and table2 is sales information

calculation goes wrong when table2 becomes like this :

expected result :

Model	ReportMonth	Count VIN1	Count VIN2
A	201901	1	0
B	201901	1	2
B	201902	1	1

BRs

Dave

Re: Need a set analysis expression

Arthur_Fong — Mon, 14 Oct 2019 10:42:43 GMT

If join is not a good option, perhaps concatenating both tables will solve the problem:

Refer qvw attached below as reference.

Thanks and regards,

Arthur Fong

Re: Need a set analysis expression

jaibau1993 — Mon, 14 Oct 2019 12:13:31 GMT

Hi! Set analysis is evaluated once per chart. This means that you can't build a "row-wise set analysis" expression but it is what you need: for each value of the dimension "ReportMonth" you need to count VIN2 where WSMonth equals the respective value of ReportMonth. Said so and given that you can not modify the script (by the way, are you sure you cant? You dont neet to join nor concatenate both tables, you maybe can create an auxiliar field that helps you solve your problem) I suggest you to use an "if" expression:

Count(if(WSMonth = ReportMonth, VIN2))

since it is row wise (but it is less efficient than set analysis so be careful).

Regards,

Jaime.