Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 
rikkeqlik
Contributor III

Load where not match two criterias

Hi, I would like to exclude certain rows from my load script witch matches both in ID and Date fields. Ex. ID=1,Date<20210103 and ID=2, Date<20210102. I have tried LOAD WHERE Not Match, but I'm not able to use to criterias. Any ideas?

ID Date
1 20210101
1 20210102
1 20210103
1 20210104
2 20210101
2 20210102
2 20210103
3 20210101
3 20210102
3 20210103
3 20210104
3 20210105
3 20210106
3 20210107
3 20210108
1 Solution

Accepted Solutions
rikkeqlik
Contributor III
Author

Found a workaround 🙂

Created qvd-files for the wanted ID's and dates. Joined them to a new wanted.qvd

Removed all relevant ID's from my original file, saved to a new cleaned.qvd, and then joined wanted.qvd and cleaned.qvd to a new full.qvd.

View solution in original post

5 Replies
AshutoshBhumkar
Partner - Specialist

Hello,

Something like below ?

Table:
Load * Inline [
ID, Date
1, 20210101
1, 20210102
1, 20210103
1, 20210104
2, 20210101
2, 20210102
2, 20210103
3, 20210101
3, 20210102
3, 20210103
3, 20210104
3, 20210105
3, 20210106
3, 20210107
3, 20210108
] Where (ID=1 and Date<'20210103') Or (ID=2 and Date<'20210102');

 

Output :

AshutoshBhumkar_0-1641459169673.png

 

rikkeqlik
Contributor III
Author

Thank you, this could work, but my wanted excluded data contains about 800 ID's and 150 dates per ID . Is there a different way?

rwunderlich
Partner Ambassador/MVP

The Dates you want to exclude, do you have the specific dates are are they < as in your example?

-Rob

rikkeqlik
Contributor III
Author

Specific dates, but not the same for all the ID's.

They all have dates -> 05.10.21 and I want to set a new "last date". Some will have 01.02.21, others 01.03.21 or 01.04.21 and so on.

rikkeqlik
Contributor III
Author

Found a workaround 🙂

Created qvd-files for the wanted ID's and dates. Joined them to a new wanted.qvd

Removed all relevant ID's from my original file, saved to a new cleaned.qvd, and then joined wanted.qvd and cleaned.qvd to a new full.qvd.