Qlik Community

Third-Party Qlik Jobs

Area for people to learn more about current Qlik related job postings.

Highlighted
rajeshqvd
Contributor

CountIFs

Hi Need to create output column in script please check below

IDCatogeryOutput
111Red1
111 1
222 2
222Green2
222 2
222Red2
111RED1
111 1
333 1
333 1
333Red1
333 1
333 1
444 0
Labels (1)
1 Solution

Accepted Solutions
mark6505
Valued Contributor III

Re: CountIFs

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;

5 Replies
mark6505
Valued Contributor III

Re: CountIFs

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
Contributor

Re: CountIFs

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
mark6505
Valued Contributor III

Re: CountIFs

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;

Re: CountIFs

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
Contributor

Re: CountIFs

Thanks for Quick response Smiley Happy
Community Browser