Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

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
Not applicable

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
Not applicable

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

Highlighted
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
Not applicable

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

Not applicable

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

Another way with not exists

Chris

anbu1984
Not applicable

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.