topic Compare, Extract & only save delta in QlikView

Compare, Extract & only save delta

Thu, 24 Oct 2013 11:56:32 GMT

Hello community,

I export a list of paths that contain IDs (which are associated to tif files) to a csv.

IDs look like this: ID00022123 or ID00022124

The path looks like this: \\server\D$\public\ID00022123.tif

I build this path with the static server string concatenated with the ID I get from a column plus the string '.tif'.

So the most recent ID is always the highest number when you take those five digits from the right.

In the DB IDs are only added not removed.

Now I want to extract the delta from a previous load. Meaning I only want to export the newly added IDs, overwriting the old entries.

How can I make this work?

Re: Compare, Extract & only save delta

swuehl — Thu, 24 Oct 2013 12:00:17 GMT

Somethinge like this should work:

Old:

LOAD ID FROM ID.qvd (qvd);

New:

NOCONCATENATE

LOAD ID FROM SourceTable WHERE not EXISTS(ID);

STORE New into ID.qvd (qvd);

Re: Re: Compare, Extract & only save delta

Fri, 25 Oct 2013 07:56:46 GMT

What am I doing wrong?

OLD:
//LOAD ID INLINE [
//    ID
//  ID00033123
//  ID00033124
//  ID00033125
//];
//store [OLD] into ID.qvd (qvd);
LOAD ID FROM ID.qvd (qvd);

NoConcatenate
Main:
LOAD * INLINE [
    ID
  ID00033123
  ID00033124
  ID00033125
  ID00066120
];

NEW:
NoConcatenate
LOAD ID Resident Main WHERE NOT EXISTS(ID);

store NEW into ID.qvd (qvd);

Re: Compare, Extract & only save delta

swuehl — Fri, 25 Oct 2013 08:09:17 GMT

After your Main table INLINE LOAD, all ID values already exist in the QV data model, so your NEW table load probably results in an empty table, right?

Either load the NEW values directly from an external data source, skipping the RESIDENT LOAD, or using it like

NEW:

NoConcatenate

LOAD ID INLINE [

ID00033123

ID00033124

ID00033125

ID00066120

];

WHERE NOT EXISTS(ID);

or using a different field name for MAIN's ID (ID as MAINID) and use exists() with two arguments: ...WHERE NOT EXISTS(ID, MAINID)

Re: Compare, Extract & only save delta

jagan — Fri, 25 Oct 2013 08:14:53 GMT

Hi,

Load Main table from Excel/CSV or from database instead of Inline. It will work.

OLD:
LOAD ID FROM ID.qvd (qvd);

NEW:
NoConcatenate
LOAD ID
FROM MaindataFileName
WHERE NOT EXISTS(ID);

store NEW into ID.qvd (qvd);

Regards,

Jagan.

Re: Re: Compare, Extract & only save delta

Fri, 25 Oct 2013 11:06:26 GMT

I didn't know about the "qvd/csv trick". Now I finally got it working.

Thank you both.