Load where not match value

[davyqliks]

Hi All, i hope you can assist with my latest issue.

In my data i have 3 status - Not Yet Posted | Posted | Cancelled

I am currently removing the cancelled lines using a where not match like this:

WHERE

not match (Status,'Cancelled')

;

However i have just found that in some instances the data which is cancelled exists as Not yet Posted from the previous days load. This means in the cancelled is not loaded but the line is not removed from the QV doc where previously received as not yet posted.

I need to not show this line as Not yet Posted.

In my source QVD, which i load into the main dashboard, the invoice shows as Not Yet Posted and Cancelled. Currently in the dashboard using the above Where Not Match clause the invoice shows just as Not Yet Posted.

I thought i may be able to use:

WHERE

not match (Status,'Cancelled' & 'Not Yet Posted');

But this did not work. 

How can i exclude the Invoice where i am loading for status for both Not yet posted and Cancelled please.

Any help would be very much appreciated.

Thank you

Daniel

[martinpohl]

Hello

with

WHERE

not match (Status,'Cancelled', 'Not Yet Posted');

you can achieve not the load both status.

Regards

[davyqliks]

Hi,

Thank you for this help.

Please confirm, this is the 2 status' grouped so it will load where an invoice exist for both?

Will this then load data where status is just cancelled?

Thanks again for your help on this.

Daniel

[davyqliks]

Hi,

I tried this like the following:

WHERE

not match (Status,'Cancelled') // to capture the just cancelled lines
and
not match (Status,'Cancelled', 'Not Yet Posted'); // to capture lines where Not Yet posted and Cancelled

I have ended up with no 'Not Yet Posted' data.

How can i associate this Where not match (Status,'Cancelled', 'Not Yet Posted') 
To apply to the field Invoice no.

Or is there a better way?

Thanks

Daniel

[Brett_Bleess]

@sunny_talwar can you have a look at this one for me, over my head! 🙂

Cheers,
Brett

[sunny_talwar]

Checking @Brett_Bleess 

[sunny_talwar]

You only have one status for each row, correct? All you want to do is to exclude IDs (generic name since I don't know what you field name is, but I am guessing this is how you check if the same ID is stamped with Not Yet Posted earlier and then switched to Cancelled later) which were stamped with Not Yet Posted, but changed to Cancelled from your load, right? I guess one thing to know is what is your ID/Key field here? Second, how are you loading your data... you can try Where not Exists by adding all IDs with Canceled in a load. These are just few ideas you can try, but to get more precise help, you will need to share some more information.

[davyqliks · Accepted solution]

Hi All,

Thank you for taking the time to look at this, 

I have found a solution for this.

first i load Invoice no like this with an as (different name) in my daily load

[Invoice no.],
[Invoice no.] as InvoiceToday,

Then when concatenating the daily with the historic data i use the below:

Sales:

LOAD *

FROM
[\QVDs\SalesLUKinc.QVD]
(qvd);

Concatenate

LOAD *

FROM
[\QVDs\LUKHistory.QVD]
(qvd)

Where not Exists(InvoiceToday,[Invoice no.]);

drop Field InvoiceToday;

STORE
Sales into \QVDs\LUKHistory.QVD(qvd);

this only loads the historic where not exists in the new therefor capturing the latest data.

Thanks again for taking the time to look into this issue

Daniel