topic Re: Data Filter in QlikView

Data Filter

brindlogcool — Sun, 01 Jul 2012 15:23:42 GMT

Hi,

I have an table with thousands of rows with audit details in it. It will have the original record and modified record in the same table.I need to consider only the modified records.The first and third records are same but i need to consider only the thrid record.

Id user Date Teamcode Reason Audit date

1 01 04/02/2012 A1 Planned 04/02/2012

2 02 04/04/2012 A2 Active 04/04/2012

3 01 04/02/2012 A2 Active 04/08/2012

I need the output as below

Id user Date Teamcode Reason Audit date

2 02 04/04/2012 A2 Active 04/04/2012

3 01 04/02/2012 A2 Active 04/08/2012

Re: Data Filter

hic — Sun, 01 Jul 2012 15:49:31 GMT

You can sort them according to Audit date, and just load the latest:

RawData:

Load

Id as TempID,

user,

Date,

Teamcode,

Reason,

[Audit date] From File;

AuditData:

Load * where not Exists(ID);

Load

TempID as ID,

user,

Date,

Teamcode,

Reason,

[Audit date]

resident RawData

order by [Audit date] desc ;

Drop Table RawData;

Re: Data Filter

brindlogcool — Sun, 01 Jul 2012 16:23:32 GMT

Hi Enric,

Thanks for the response.

As per your code you are just sorting it. Where you are filtering it .

Could you please explain your code logic.

Re: Data Filter

hic — Sun, 01 Jul 2012 16:56:09 GMT

The "Load * where not Exists(ID)" is a filter that loads only those IDs that have not been loaded before.

HIC

Re: Data Filter

Anonymous — Sun, 01 Jul 2012 21:50:12 GMT

Planned_Table:

Load

TempID as ID,

user as Planned_User,

Date as Planned_Date,

Teamcode as Planned_Team_Code,

1 as Planned_Flag,

[Audit date] as Planned_Date

From File

Where Reason = 'Planned';

Join (Planned_Table)

Load

TempID as ID,

user as Active_User,

Date as Active_Date,

Teamcode as Active_Team_Code,

1 as Active_Flag,

[Audit date] as Active_Date

From File

Where Reason = 'Active';

/// The above create a table with a 'Planned Record' and any corresponding 'Active' record on the same row for a user id.

This can then be used for table queries, set analysis etc.