Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
lukeert19
Contributor III
Contributor III

replace exel formula in qlik

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

2 Solutions

Accepted Solutions
Taoufiq_Zarra

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 :

Capture.PNG

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉

View solution in original post

Saravanan_Desingh

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
];

commQV98.PNG

View solution in original post

7 Replies
Taoufiq_Zarra

can you share a sample data and the expected output ?

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉
lukeert19
Contributor III
Contributor III
Author

yes sure,

 

axample I have:

 

idcolumn1column2column3column4
1234560201
7894651110
7412580232

 

final result will be

idcolumn1column2column3column4check
12345602011
78946511100
74125802323

 

 

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

 

thanks in advance

Luke

 

Taoufiq_Zarra

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 :

Capture.PNG

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉
Saravanan_Desingh

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
];

commQV98.PNG

lukeert19
Contributor III
Contributor III
Author

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;

commQV01.PNG

lukeert19
Contributor III
Contributor III
Author

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