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: 
rajeshqvd
Creator II
Creator II

CountIFs

Hi Need to create output column in script please check below

ID Catogery Output
111 Red 1
111   1
222   2
222 Green 2
222   2
222 Red 2
111 RED 1
111   1
333   1
333   1
333 Red 1
333   1
333   1
444   0
Labels (1)
1 Solution

Accepted Solutions
Mark_Little
Luminary
Luminary

DATA:
LOAD
ID,
UPPER(Catogery) as Catogery
FROM [lib://QlikTest/Book1.xlsx]
(ooxml, embedded labels, table is Sheet1);

LEFt Join

Load
ID,
COUNT(DISTINCT Catogery) as output
Resident DATA
GROUP by ID;

View solution in original post

5 Replies
Mark_Little
Luminary
Luminary

HI,

Can you please provide what is the logic behind the output Column.

How do you decide is it is 0, 1 or  2?

Mark

rajeshqvd
Creator II
Creator II
Author

Based on Category if you see the data 111 has only red thats why its 1 and 222 has green and red thats why its 2 finally need to know how many categories for id
Mark_Little
Luminary
Luminary

DATA:
LOAD
ID,
UPPER(Catogery) as Catogery
FROM [lib://QlikTest/Book1.xlsx]
(ooxml, embedded labels, table is Sheet1);

LEFt Join

Load
ID,
COUNT(DISTINCT Catogery) as output
Resident DATA
GROUP by ID;

sunny_talwar

ID 111 seems to be repeating... is that normal... If it is... do you DISTINCT count Category across the same ID or do you do this based on each occurrence of an ID?
rajeshqvd
Creator II
Creator II
Author

Thanks for Quick response 🙂