Count IF Multiple Conditions

[Suflour]

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,

[Arthur_Fong · Accepted solution]

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

[Suflour]

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

Thanks, Suflower.

[Arthur_Fong · Accepted solution]

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