topic Re: Count IF Multiple Conditions in QlikView

Count IF Multiple Conditions

Suflour — Sat, 16 Nov 2024 01:43:44 GMT

Hi guys,

I would just like to seek help on how I can COUNT the occurrence of values greater AND smaller than 10.
For example, I have in the table:

ID 1000.

ID	Num1	Num2	Num3	Num4
1000	1	20	30	40
1001	1	2	50	60
1002	4	5	70	80
1003	3	5	90	100
1004	1	2	3	20
1005	7	8	9	50

Numbers <= 10: only number 1;
Numbers > 10: numbers 20, 30, 40;

So the result would be:
There are 1 number <= 10 and 3 numbers > 10 -- 1 low 3 high

For the ID 1001, 2 low 2 high and so on.

And the graph would be as follows (see attachment):

My script is attached.

//--------------------------------------------------

TBL_LOAD:

LOAD * INLINE [
ID, Num1, Num2, Num3, Num4
1000, 1, 20, 30, 40
1001, 1, 2, 50, 60
1002, 4, 5, 70, 80
1003, 3, 5, 90, 100
1004, 1, 2, 3, 20
1005, 7, 8, 9, 50
];
//-------------------------------

TRANSFORM_CONCAT:
LOAD
ID,

//Concatenate numbers separated by the "blank" string row by row

[Num1]&' '&[Num2]&' '&[Num3]&' '&[Num4] as Num_Concat

//1 20 30 40
//1 2 50 60
//and so on...

Resident TBL_LOAD;

//-------------------------------

TRANSFORM_NUMBERS:

LOAD

//Create Numbers dimension with all numbers

Num_Concat,
subfield(Num_Concat,' ') as Numbers

Resident TRANSFORM_CONCAT;

TRANSFORM_LOW_HIGH:

LOAD Numbers,
// Here, I'm utterly confused by my count if expressions

If(count(if(Numbers<=10,Numbers)) =1 AND count(if(Numbers>10,Numbers))=3, '1 Low 3 High',
If(count(if(Numbers<=10,Numbers)) =2 AND count(if(Numbers>10,Numbers))=2, '2 Low 2 High',
If(count(if(Numbers<=10,Numbers)) =3 AND count(if(Numbers>10,Numbers))=1, '3 Low 1 High',

'Others'))) as Count_Low_High

Resident TRANSFORM_NUMBERS;

Best regards,

Re: Count IF Multiple Conditions

Arthur_Fong — Wed, 11 Dec 2019 03:20:11 GMT

I would prefer this way:

Script:

TBL_LOAD: LOAD * INLINE [ ID, Num1, Num2, Num3, Num4 1000, 1, 20, 30, 40 1001, 1, 2, 50, 60 1002, 4, 5, 70, 80 1003, 3, 5, 90, 100 1004, 1, 2, 3, 20 1005, 7, 8, 9, 50 ]; CrossTable(Numbers,Value) Data: load * resident TBL_LOAD; drop table TBL_LOAD; tempData: load *, if(Value<10,'Low','High') as Flag Resident Data; //exit script; tempData2: load ID,Flag,count(Flag)&' '&Flag as Flag2, ID&Flag as Key Resident tempData group by ID,Flag order by ID asc,Flag desc; drop table tempData; drop table Data; //exit Script; NoConcatenate tempData3: load ID, concat(Flag2,' ',len(Flag))as Flag3 resident tempData2 group by ID order by Key desc; drop table tempData2; exit script;

Thanks and regards,

Arthur Fong

Re: Count IF Multiple Conditions

Suflour — Thu, 12 Dec 2019 01:48:23 GMT

Thank you very much, Arthur for this suggestion, works fine.

Thanks, Suflower.

Re: Count IF Multiple Conditions

Arthur_Fong — Thu, 12 Dec 2019 01:54:05 GMT

If this works, please accept the post above as solution.