Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Suflour
New Contributor

Count IF Multiple Conditions

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.

IDNum1Num2Num3Num4
10001203040
1001125060
1002457080
10033590100
100412320
100578950

 

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,

2 Solutions

Accepted Solutions
Partner
Partner

Re: Count IF Multiple Conditions

I would prefer this way:

MC.PNG

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

View solution in original post

Partner
Partner

Re: Count IF Multiple Conditions

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

 

View solution in original post

3 Replies
Partner
Partner

Re: Count IF Multiple Conditions

I would prefer this way:

MC.PNG

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

View solution in original post

Suflour
New Contributor

Re: Count IF Multiple Conditions

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

Thanks, Suflower.

Partner
Partner

Re: Count IF Multiple Conditions

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

 

View solution in original post