replace exel formula in qlik

[lukeert19]

Hello,

I have a question,

normally in Excel I need to use the formula below

=COUNT.PLUS.ID(BM2:CF2;"CODE*")

BM2 is the first column named Part1

BN2 is the second column named Part2

BO2 is the third column named Part3

and so on...

how can I replace it on qlik ?

Thanks in advance

Luca

[Taoufiq_Zarra]

can you share a sample data and the expected output ?

[lukeert19]

yes sure,

axample I have:

id	column1	column2	column3	column4
123456	0	2	0	1
789465	1	1	1	0
741258	0	2	3	2

final result will be

id	column1	column2	column3	column4	check
123456	0	2	0	1	1
789465	1	1	1	0	0
741258	0	2	3	2	3

excel formula which I have used in this example is: =count.plus.if(B2:E2;">1")

thanks in advance

Luke

[Taoufiq_Zarra · Accepted solution]

One Solution :

let chek=1;

Data:
LOAD  RangeSum(if(column1>$(chek),1,0),if(column2>$(chek),1,0),if(column3>$(chek),1,0),if(column4>$(chek),1,0)) as check, * INLINE [
    id, column1, column2, column3, column4
    123456, 0, 2, 0, 1
    789465, 1, 1, 1, 0
    741258, 0, 2, 3, 2
];

output :

[Saravanan_Desingh · Accepted solution]

One more solution is.

tab1:
LOAD *, -RangeSum(column1>1, column2>1, column3>1, column4>1) As Check;
LOAD * INLINE [
    id, column1, column2, column3, column4
    123456, 0, 2, 0, 1
    789465, 1, 1, 1, 0
    741258, 0, 2, 3, 2
];

[lukeert19]

Saran7de I don't know if  you remember in one my recent post "pivot table in load statement" you have suggest me to use generic load[with funtastic result 🙂 ]. I need  to add this kind of check column   inside the script below

Gen:
Generic
LOAD serial3, taskLabel, Count(taskLabel) As Cnt
Resident tab1
Group By serial3, taskLabel;

 for you, is possible to add this kind of  column also if I don't have directly the name of each columns?

[Saravanan_Desingh]

@lukeert19  - Are you looking something like this?

tab1:
LOAD * Inline [
serial3, taskLabel
12345, taskXxxxx
12345, taskyyyyy
12345, taskyyyyy
12345, taskxxxYyy
54321, taskXxxxx
54321, taskxxxYyy
54321, taskWWWW
54321, taskWWWW
];

Gen:
Generic
LOAD serial3, taskLabel, -(Count(taskLabel)>1) As Cnt
Resident tab1
Group By serial3, taskLabel
;

Drop Table tab1;

[lukeert19]

Yes, here is possible to put also che check column?