Discussion Board for collaboration on QlikView Scripting.
I'm searching for the equivalent of this SQL code in Qlikview:
select ID,CODE, COUNT(*) OVER (PARTITION BY ID) as count_ID from TABLE group by 1,2
Thank you in advance.
If the direct sql code isn't working then I think the only way in Qlikview script is to left join itself ...
select ID, CODE from TABLE group by 1,2; //sql code
load ID, count(ID) as count_ID resident tmp group by ID;
ID,CODE, COUNT(*) OVER (PARTITION BY ID) as count_ID
group by 1,2;
thank you for your response,
Actually I have a table stored in a qvd like below:
ID Code Info1 Info2 Info3 ……
and i want to add a new field (Count_ID) in the script which indicate the ID present more than twice.(like below)
ID Code Count_ID Info1 Info2 Info3 ……
01 A 1
02 B 2
05 C 2
07 X 1
05 Y 2
10 D 1
02 F 2
Would something like this help ?
LOAD * INLINE [
count(ID) as CountID ,
group by ID
order by ID
drop table Temp ;