topic Re: Help to display distinct record based on custcode. in QlikView

Help to display distinct record based on custcode.

Tue, 24 Jan 2017 02:12:03 GMT

Hi All,

Please help me to display the records based on cust code.

Custid	Timestamp	Statid	Custcode	Custreason
1	6/13/2016 10:09:49	7905	A	trigger
1	6/13/2016 10:09:49	7905	M	trigger
2	7/15/2016 23:07:24	6304	A	trigger
2	7/15/2016 23:07:24	6304	M	trigger

Expected out put:

Custid	Timestamp	Statid	Custcode	Custreason
1	6/13/2016 10:09:49	7905	M	trigger
2	7/15/2016 23:07:24	6304	M	trigger

Thanks in advance.

Re: Help to display distinct record based on custcode.

sunny_talwar — Tue, 24 Jan 2017 02:23:36 GMT

May be just this:

LOAD Custid,

Timestamp,

Statid,

Custcode

Custreason

FROM ....

Where Custcode = 'M';

Re: Help to display distinct record based on custcode.

Tue, 24 Jan 2017 03:10:24 GMT

Thanks for reply sunny. I missed one scenario.

Please find below.

my data

Custid	Timestamp	Statid	Custcode	Custreason
1	6/13/2016 10:09:49	7905	A	trigger
1	6/13/2016 10:09:49	7905	M	trigger
2	7/15/2016 23:07:24	6304	A	trigger
2	7/15/2016 23:07:24	6304	M	trigger
3	6/20/2016 18:08:36	5102	A	trigger

Expected Output
Custid	Timestamp	Statid	Custcode	Custreason
1	6/13/2016 10:09:49	7905	M	trigger
2	7/15/2016 23:07:24	6304	M	trigger
3	6/20/2016 18:08:36	5102	A	trigger

Please advice.

Thanks,

Re: Help to display distinct record based on custcode.

sunny_talwar — Tue, 24 Jan 2017 03:14:15 GMT

May be like this:

Table:

LOAD Custid,

Timestamp,

Statid,

Custcode

Custreason

FROM ....;

Left Join (Table)

LOAD Custid,

Count(Custid) as Count

Resident Table

Group By Custid;

FinalTable:

NoConcatenate

LOAD *

Resident Table

Where Count = 1 or Custcode = 'M';

DROP Table Table;

Re: Help to display distinct record based on custcode.

sunny_talwar — Tue, 24 Jan 2017 03:16:55 GMT

Sample attached

Table:

LOAD * INLINE [

Custid, Timestamp, Statid, Custcode, Custreason

1, 6/13/2016 10:09:49, 7905, A, trigger

1, 6/13/2016 10:09:49, 7905, M, trigger

2, 7/15/2016 23:07:24, 6304, A, trigger

2, 7/15/2016 23:07:24, 6304, M, trigger

3, 6/20/2016 18:08:36, 5102, A, trigger

];

Left Join (Table)

LOAD Custid,

Count(Custid) as Count

Resident Table

Group By Custid;

FinalTable:

NoConcatenate

LOAD *

Resident Table

Where Count = 1 or Custcode = 'M';

DROP Table Table;

Re: Help to display distinct record based on custcode.

Tue, 24 Jan 2017 03:28:57 GMT

Thank you sunny. I will try in my original application and let you know the update.