topic Re: deleting record from previous table during load in QlikView

deleting record from previous table during load

danielgargiulo — Sat, 26 Jul 2014 07:17:14 GMT

HI All,

I have several hundred files to load all with the exact same fields. I am currently using:

NMDS:

Load*

FROM

[..\..\8.Import\0.NMDS\2013\*.txt]

(txt, codepage is 1252, no labels, delimiter is ',', msq);

One requirement is to look for 'D1' records in the 'Message' Field. If it finds a 'D1' record then i need to search the records i have loaded so far for the matching Primary Key and Delete (or Flag) the matching record that has previously been loaded. Any suggestions on how this could be achieved?

Kind regards,

Daniel

Re: deleting record from previous table during load

Sat, 26 Jul 2014 20:59:20 GMT

Hi Daniel,

Let me know if this works:

Message_inline:

load * inline

[Message

D1];

NMDS:

Load*

FROM

[..\..\8.Import\0.NMDS\2013\*.txt]

(txt, codepage is 1252, no labels, delimiter is ',', msq)

where exists(Message);

Continue to use where exists() function on all your files. This will ensure that only records relevant to D1 are loaded and rest are ignored. Flagging them is a bigger problem. Avoiding records is easier.

Re: deleting record from previous table during load

Anonymous — Sat, 26 Jul 2014 22:56:37 GMT

Hi Daniel,

I hope this can be useful. Assuming that you have in memory a load of the previous files:

1- Load your current file:

NMDS:

Load*

FROM

[..\..\8.Import\0.NMDS\2013\*.txt]

(txt, codepage is 1252, no labels, delimiter is ',', msq);

2- Left join a flag with those IDs that match your requirement:

LOAD

Resident Previous_Loaded_Records

Left Join

Load:

1 as [Delete Flag]

Resident NMDS

Where Match(Message, 'D1') > 0;

Regards,

Re: deleting record from previous table during load

rwunderlich — Sat, 26 Jul 2014 23:13:56 GMT

I think I understand your question, but can you post a bit of sample data?

-Rob

Re: Re: deleting record from previous table during load

danielgargiulo — Sat, 26 Jul 2014 23:23:36 GMT

HI Rob,

Please see a QVW attached with a basic load inline statement that hopefully illustrates the concept. Note: In reality there are hundreds of tables to load. I don't think a join after the load will work as there will always be a new record with the same PK that gets added once the old one has been deleted or flagged.

Thanks,

Dan

Re: Re: Re: deleting record from previous table during load

rwunderlich — Sun, 27 Jul 2014 19:38:55 GMT

If the delete is always followed by a new insert, could the problem be restated as you want only the last record for each PK? if so, how about:

DataTable:

LOAD *, AutoNumber(RecNo(), PK) as Seq INLINE [

PK, Message, Value

1, A1, 34

2, A1, 54

3, A1, 67

4, A1, 89

];

LOAD *, AutoNumber(RecNo(), PK) as Seq INLINE [

PK, Message, Value

5, A1, 87

6, A1, 23

2, D1,

2, A1, 80

];

INNER JOIN (DataTable)

LOAD

PK,

Max(Seq) as Seq

RESIDENT DataTable

GROUP BY PK

;

Will that work or am I being naive about the data?

-Rob

http://masterssummit.com

http://robwunderlich.com

Re: Re: Re: deleting record from previous table during load

danielgargiulo — Mon, 28 Jul 2014 02:38:49 GMT

Hi Rob,

No you are not being naive and have produced a fantastic solution. I think i was over complicating this!! Your solution works based on the sample provided and I will test on the full data in the next day or two.

The delete may not always be followed by a new insert however if it is not then the delete record would be the one left and these will be excluded anyway. The only thing I need to ensure is they are ordered 'D1' then 'A1' as the records are loaded from each file.

Thanks for your help and enjoy the week ahead.

Dan