topic Re: Update records after loading in QlikView

Update records after loading

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

Hi Experts,

I have loaded an excel file and have few update statements that needs to update the records that are loaded already.

Below is sample one

ACCOUNT:

NAME NO AMOUNT DEPT

ABC 10 5000 HR

DEF 10 5500 IT

GHI 20 2000 IT

Now I have update statement that are in sql. like below

Update ACCOUNT SET NAME='XYZ' where NO=10 and DEPT='IT'

Is there any way to achieve the above ?

Thanks,

Vivek

Re: Update records after loading

muthukumar77 — Wed, 08 Mar 2017 10:18:34 GMT

Hi,

No, We can't do update process in qlikview. Qlikview for only analysis the data.

Re: Update records after loading

Anil_Babu_Samineni — Wed, 08 Mar 2017 10:31:06 GMT

Can you check this on your environment

Sales:

LOAD * INLINE [

ID, Name, Sales

1, Anil, 10

2, Babu, 20

3, Samineni, 30

];

STORE Sales into Sales.qvd (qvd);

DROP Table Sales;

Sales:

LOAD ID,

Name,

If(Name = 'Samineni', 40, Sales) as Sales

From Sales.qvd (qvd);

STORE Sales into Sales.qvd (qvd);

Re: Update records after loading

adamdavi3s — Wed, 08 Mar 2017 10:31:22 GMT

The only way you could do this is to do some preceding loads or mapping loads or left joins but its possible for sure.

Re: Update records after loading

adamdavi3s — Wed, 08 Mar 2017 10:38:14 GMT

Here is the code for left joins to do it, its a bit fiddly but it works

ACCOUNT:
LOAD * INLINE [
NAME,  NO, AMOUNT, DEPT,
ABC,    10,  5000,  HR,
DEF,    10,  5500,  IT,
GHI,    20,  2000 ,  IT,

];
//do a left join to update a new column
LEFT JOIN (ACCOUNT)


LOAD 'XYZ' as NEWNAME, 10 as NO, 'IT' as DEPT
autogenerate(1);

//second left join to fill in the blanks

LEFT JOIN (ACCOUNT)
LOAD IF(len(NEWNAME)<1,NAME,NEWNAME) as FINALNEWNAME, NO, AMOUNT,DEPT
RESIDENT ACCOUNT;

DROP FIELDS NAME,NEWNAME;

RENAME FIELD FINALNEWNAME to NAME;

Re: Update records after loading

adamdavi3s — Wed, 08 Mar 2017 10:40:48 GMT

Here is a simple preceding load

ACCOUNT:
LOAD NO,AMOUNT,DEPT,if(NO=10 AND DEPT='IT','XYZ',NAME) as NAME;
LOAD * INLINE [
NAME,  NO, AMOUNT, DEPT,
ABC,    10,   5000,  HR,
DEF,    10,  5500,   IT,
GHI,     20,  2000 ,  IT,
];

Re: Update records after loading

hemachandran — Wed, 08 Mar 2017 12:15:11 GMT

HI,

Its not possible in Qlikview. We cant add or change the data once loaded..If we want to change the data we have to do it in the data source and reload only.

Re: Update records after loading

adamdavi3s — Thu, 09 Mar 2017 07:30:14 GMT

did this help? if so please remember to mark this as helpful or the correct answer if I have helped you or answered your question. This will help users identify the answers should they come across this thread in the future.