topic group by query in QlikView

group by query

Tue, 04 Aug 2009 17:57:19 GMT

Hi Team,

LC	PC
AGRA	28
AHMEDABAD	38
AHMEDNAGAR	41
AHMEDNAGAR	41
AHMEDNAGAR	42
AIZWAL	79
AJC BOSE RD	70
AMRAVATI	44
AMRELI	35
AMRELI	36

Above is the sample data. I have to find out LCs appearing in different PC eg in above sample data Ahmednagar and Amreli are two LCs where PCs are different.

I can use Peek function for this, eg.

Load LC,PC
Resident Sample_Data
Where LC = Peek(LC) And PC <> Peek(PC);

But there is one problem with this is that i will have only

AHMEDNAGAR	42
AMRELI	36

means where LC is same and PC is different.

But for comparison, I want the output as below:

AHMEDNAGAR	41
AHMEDNAGAR	42
AMRELI	35
AMRELI	36

means all distinct records where PCs are different.

Any suggestion!!

I know the use of Where Not Exist(LC) but can i use eg.

Where Not Exist (LC) And Not Exist (PC)
OR Not Exist (LC.PC)

group by query

Tue, 04 Aug 2009 19:19:51 GMT

I was thinking you could combine LC and PC to create a unique key and just not load the one that are found more than once.

tmp:

LOAD

LC&' '&PC as ID,

LC,

Resident Sample_Data;

myTable:

noconcatenate load

ID,

LC,

resident tmp where ID <> peek(ID);

drop table tmp;

Good luck!

BR,

Ida

group by query

johnw — Sat, 08 Aug 2009 06:39:58 GMT

How about this?

LOAD LC
FROM data source
WHERE LC = peek(LC)
AND PC <> peek(PC)
ORDER BY LC, PC
;
LEFT JOIN
LOAD LC, PC
FROM data source
;