Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
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.
Issam
Hi Issam,
If the direct sql code isn't working then I think the only way in Qlikview script is to left join itself ...
tmp:
Load *;
select ID, CODE from TABLE group by 1,2; //sql code
left join
load ID, count(ID) as count_ID resident tmp group by ID;
flipside
Hi
Load *;
sql select
ID,CODE, COUNT(*) OVER (PARTITION BY ID) as count_ID
from TABLE
group by 1,2;
good luck
thank you for your response,
Actually I have a table stored in a qvd like below:
ID Code Info1 Info2 Info3 ……
01 A
02 B
05 C
07 X
05 Y
10 D
02 F
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
Regards,
Issam
Would something like this help ?
LOAD * INLINE [
ID, Code
01, A
02, B
05, C
07, X
05, Y
10, D
02, F
];
table1 :
load
count(ID) as CountID ,
ID
resident Temp
group by ID
order by ID
;
left join
load
*
resident Temp
;
drop table Temp ;