Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Exclude a set of data if a specific record exists within the data


I have the following set of data where an object number will have many different staus

objectnoFormattedNumber1projectstatus
PR0000658802.000409.01.01E0001
PR0000658802.000409.01.01I0001
PR0000658802.000409.01.01I0002
PR0000658802.000409.01.01I0045
PR0000658802.000409.01.01I0046
PR0000658802.000409.01.01I0118

Where the project status = I0046 I want to exclude all of the records applicable to the object number,if I0046 doesn't exist then I would want to display the records

Can anyone help

15 Replies
anbu1984
Honored Contributor III

Re: Exclude a set of data if a specific record exists within the data

T1:
LOAD * Inline [
objectno,FormattedNumber1,projectstatus
PR00006588,02.000409.01.01,E0001
PR00006588,02.000409.01.01,I0001
PR00006588,02.000409.01.01,I0002
PR00006588,02.000409.01.01,I0045
PR00006588,02.000409.01.01,I0046
PR00006588,02.000409.01.01,I0118
PR00006589,02.000409.01.01,I0118 ]
;

Inner Join(T1)
Load * Where IsNull(Max);
Load objectno,MaxString(If(projectstatus='I0046',projectstatus)) As Max Resident T1 Group By objectno;

Not applicable

Re: Exclude a set of data if a specific record exists within the data

Thanks

The data is coming from a QVD file and therfore all my data will be loaded for multiple object numbers, what I need is the code to exclulde object numbers where the project status = I0046.

anbu1984
Honored Contributor III

Re: Exclude a set of data if a specific record exists within the data

If you run the above script, Objectno PR00006588 is excluded since it has project status = I0046 and PR00006589 is retained

Not applicable

Re: Exclude a set of data if a specific record exists within the data

Thanks...I've managed to exclude the I0046 records but I only want to see one line for the rest of my data. How can I elminate the duplicate lines

Company CodePlantProjectno.WBS ElementWBS Desc.MarketProject ManagerMilestoneMilestone Desc.projectstatusActual DateScheduled Date
US05USP202.00040902.000409.90.00BASIN ELECTRIC Warranty  Phase251Hamilton, Phil         CO13450Start of System WarrantyE00012012-07-312012-07-31
US05USP202.00040902.000409.90.00BASIN ELECTRIC Warranty  Phase251Hamilton, Phil         CO13450Start of System WarrantyI00012012-07-312012-07-31
US05USP202.00040902.000409.90.00BASIN ELECTRIC Warranty  Phase251Hamilton, Phil         CO13450Start of System WarrantyI00022012-07-312012-07-31
US05USP202.00040902.000409.90.00BASIN ELECTRIC Warranty  Phase251Hamilton, Phil         CO13450Start of System WarrantyI00282012-07-312012-07-31
US05USP202.00040902.000409.90.00BASIN ELECTRIC Warranty  Phase251Hamilton, Phil         CO13450Start of System WarrantyI01182012-07-312012-07-31
anbu1984
Honored Contributor III

Re: Exclude a set of data if a specific record exists within the data

Which row you want to retain? Any row is fine out of duplicates?

Not applicable

Re: Exclude a set of data if a specific record exists within the data

Yes... after removing the status of I0046 I just want to see one project status record, but I can't do it based on the maining codes as there could be many different codes in the data. I just need to aggregate the data down to one line

Highlighted
Not applicable

Re: Exclude a set of data if a specific record exists within the data

Another way with not exists

Chris

anbu1984
Honored Contributor III

Re: Exclude a set of data if a specific record exists within the data

T1:
LOAD * Inline [
objectno,FormattedNumber1,projectstatus
PR00006588,02.000409.01.01,E0001
PR00006588,02.000409.01.01,I0001
PR00006588,02.000409.01.01,I0002
PR00006588,02.000409.01.01,I0045
PR00006588,02.000409.01.01,I0046
PR00006588,02.000409.01.01,I0118
PR00006589,02.000409.01.01,I0118
PR00006589,02.000409.01.01,I0002 ]
;

Inner Join(T1)
Load * Where IsNull(Max);
Load objectno,MaxString(If(projectstatus='I0046',projectstatus)) As Max,MinString(projectstatus) As projectstatus Resident T1 Group By objectno;

Not applicable

Re: Exclude a set of data if a specific record exists within the data

Hi

Still having an issue with this code. The data is loading ok but where the status = I0046 it is not excluding it from the data but returning NULL. Where I0046 is not present then it seems to be working and only returning one record.