topic Help required in QlikView

Help required

Anonymous — Fri, 29 Jul 2016 16:58:50 GMT

Hi All,

There is a table consisting of two fields, which gets updated two times a day.

I need to get incremental logic implemented for the second time reload.

See as per attached sample:

A run will always be a full load.

And in B run incremental will be applied.

So For B run I just want the records which are either Inserted , Updated or deleted.

Primary key can be [vehicle Code] &'-'&[Sales Code],

As per sample my output after B Run Should be :

Vehicle Code	Sales Code
B	S1
D	S12
D	S11
E	S1
E	S3

Re: Help required

sudeepkm — Fri, 29 Jul 2016 17:44:14 GMT

If I get time then I may create one but as of now I think you may use a field to identify the Reload time.

since it will run two times a day so must be aware of the exact time the job runs. so if the reload time is already in today then it will be the second time and you can run the script for incremental load else it should be a full load from source table.

Re: Help required

robert_mika — Fri, 29 Jul 2016 17:57:31 GMT

Do you want to keep B S1 or B S2?

B S2 was deleted...

Re: Help required

Anonymous — Fri, 29 Jul 2016 18:01:37 GMT

HI Robert ,

I require

B S1

Because with these values only I will be able to do my further transformations.

Regards

Nitin

Re: Help required

robert_mika — Fri, 29 Jul 2016 18:13:31 GMT

Req:

So For B run I just want the records which are either Inserted , Updated or deleted.

B S2 has been deleted

B S1 remains

but you are saying that you need to keep B S1.

Are they only 2 entry per Vehicle?

Why not keep A S1 then.?

Re: Help required

Anonymous — Fri, 29 Jul 2016 18:33:00 GMT

Hi Robert,

I want the records which are Inserted or Updated,

And if the record from A run is deleted then all other combinations of Vehicle code and Sales Code should also be available for that specific Vehicle Code.

No there may be several Sales Code for a particular Vehicle.

So as B S2 is deleted , so S1 for B is also required.

and there was no change for A , so That entry is not required in b run

Re: Help required

settu_periasamy — Fri, 29 Jul 2016 18:47:47 GMT

May be try this..

Directory;

//One Time Reload
//A:
//LOAD [Vehicle Code]&'-'&[Sales Code] as Key,
//   [Vehicle Code],
//     [Sales Code]
//FROM
//[TEST (1).xlsx]
//(ooxml, embedded labels, table is [A RUN]);
//
//STORE A into A.qvd(qvd);
//
//DROP Table A;

A:
LOAD Key as temp_key,
[Vehicle Code],
[Sales Code]
FROM
A.qvd
(qvd);

NoConcatenate

B:
LOAD [Vehicle Code]&'-'&[Sales Code] as Key,
[Vehicle Code],
[Sales Code]
FROM
[TEST (1).xlsx]
(ooxml, embedded labels, table is [B RUN]) Where not Exists(temp_key,[Vehicle Code]&'-'&[Sales Code]);

Concatenate(B)

LOAD temp_key as Key,
[Vehicle Code],
[Sales Code]
Resident A Where not Exists(Key,temp_key);

Inner Join(B)
LOAD [Vehicle Code]&'-'&[Sales Code] as Key,
[Vehicle Code],
[Sales Code]
FROM
[TEST (1).xlsx]
(ooxml, embedded labels, table is [B RUN]);

DROP Table A;
EXIT Script;

Sample attached