Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
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

Not applicable

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

Another way with not exists

Chris

Highlighted
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.

Community Browser