Qlikview equivalent of over partition

[#1]

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

[flipside]

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

[fkeuroglian]

Hi

Load *;

sql select

ID,CODE, COUNT(*) OVER (PARTITION BY ID) as count_ID

from TABLE

group by 1,2;

good luck

[#4]

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

[Anonymous]

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 ;