topic Re: Delete record in QlikView

Delete record

mromrod22 — Wed, 25 Nov 2020 16:16:04 GMT

Hi community,

I need drop a record in a table. Explain me with a example:

I have this table like history:

ID, NAME, MONTHYEAR, TIME, AGE

1, Ned, 012018, 22:00:13, 32,

2, George, 012018, 22:18:45, null,

3, Phill, 012018, 23:02:25, null,

4, Lucas, 012018, 23:15:00, 19,

5, Fred, 012018, 23:55:59, 28;

In latest load, I receive the age of Fred, and must update the row. Insert new record:

ID, NAME, MONTHYEAR, TIME, AGE

3, Phill, 022018, 23:02:25, 28,

6. Joe, 022018, 15:15:15, 30;

And now must drop the record of this name with age null.

The expected result:

ID, NAME, MONTHYEAR, TIME, AGE

1, Ned, 012018, 22:00:13, 32,

2, George, 012018, 22:18:45, null,

3, Phill, 012018, 23:02:25, 28,

4, Lucas, 022018, 23:15:00, 19,

5, Fred, 022018, 23:55:59, 28,

6, Joe, 022018, 15: 15:15, 30;

All the process must be dinamic.

I must drop a record of history, and replace with new row.

Any help, please?

Very thanks.

Re: Delete record

PrashantSangle — Thu, 04 Jan 2018 11:08:38 GMT

It is type of Incremental load in qlikview.

Incremental Load on QVD

Regards

Re: Delete record

mdmukramali — Thu, 04 Jan 2018 12:07:24 GMT

Hi,

FYI...

Data_temp:

LOAD

Date(Max(Date#(MONTHYEAR,'MMYYYY')),'MMYYYY') as MaxDate

FROM

(qvd);

LET vMaxYear=Peek('MaxDate',0,'Data_temp');

DROP Table Data_temp;

Employees:

LOAD * Inline [

ID, NAME, MONTHYEAR, TIME, AGE

3, Phill, 022018, 23:02:25, 28

6, Joe, 022018, 15:15:15, 30

]

Where Date#(MONTHYEAR)>$(vMaxYear);

Concatenate(Employees)

LOAD * Inline

[

ID, NAME, MONTHYEAR, TIME, AGE

1, Ned, 012018, 22:00:13, 32

2, George, 012018, 22:18:45,

3, Phill, 012018, 23:02:25,

4, Lucas, 012018, 23:15:00, 19

5, Fred, 012018, 23:55:59, 28

]

Where NOT Exists(ID);

STORE Employees into D:\QVW Files\Employees.qvd(qvd);