Count Function in qlik sense

[its_rajvir]

Dear Experts,

Can anyone please help me to find out the answer:

My requirement is to select the ID's from the database who Visited only once.

Please see below the sample dataset:

Date	ID	Transaction Type
1/8/2022	201	Entry
1/8/2022	201	Exit
2/8/2022	202	Entry
2/8/2022	202	Exit
2/8/2022	203	Entry
2/8/2022	203	Exit
5/8/2022	202	Entry
5/8/2022	202	Exit
7/8/2022	203	Entry
7/8/2022	203	Exit
7/8/2022	204	Entry
7/8/2022	204	Exit

Thanks

[JordyWegman]

Hi Rajvir,

What is the definition of once? Once per day? Or once in the whole data set?
I assumed that all entries have an exit and you want to see if people have multiple entries. If so filter them out and you are left with only the ones that are unique.

Try this:

Table:
Load * Inline [
Date, 	ID, 	Transaction Type
1/8/2022, 	201, 	Entry
1/8/2022,	201, 	Exit
2/8/2022, 	202, 	Entry
2/8/2022, 	202, 	Exit
2/8/2022, 	203, 	Entry
2/8/2022, 	203, 	Exit
5/8/2022, 	202, 	Entry
5/8/2022, 	202, 	Exit
7/8/2022, 	203, 	Entry
7/8/2022, 	203, 	Exit
7/8/2022, 	204, 	Entry
7/8/2022, 	204, 	Exit
];


FilterTable:
Load
	ID as IdFilter
Where _indIdCount = 1
;
Load
    Count( ID) as _indIdCount,
    ID
Resident Table
Where [Transaction Type] = 'Entry'
Group by ID
;

NoConcatenate
Final:
Load	
	*
Resident Table
Where Exists( IdFilter , ID )
;

Drop table Table;


Exit script

Jordy

Climber