Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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 :
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
];
can you share a sample data and the expected output ?
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
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 :
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
];
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?
@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;
Yes, here is possible to put also che check column?