topic Re: Update in Qlik script in QlikView

Update in Qlik script

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

Hi All,

I want to add one column in my existing created table called 'state' column name should be "sid" and after that i have to write below sql update statement in qlik.

UPDATE a set sid = 1 FROM state a where a.statename='MAH'

UPDATE a set sid = 1 FROM state a where a.statename='MAH1'

UPDATE a set sid = 2 FROM state a where a.statename='KAS'

UPDATE a set sid = 3 FROM state a where a.statename='TAM'

UPDATE a set sid = 4 FROM state a where a.statename='PAN'

UPDATE a set sid = 5 FROM state a where a.statename='HP'

UPDATE a set sid = 6 FROM state a where a.statename='UP'

UPDATE a set sid = 6 FROM state a where a.statename='MP'

UPDATE a set sid = 7 FROM state a where a.statename='RJ'

UPDATE a set sid = 9 FROM state a where a.statename='OS'

UPDATE a set sid = 11 FROM state a where a.statename='GOA'

UPDATE a set sid = 12 FROM state a where a.statename='JP'

UPDATE a set sid = 12 FROM state a where a.statename='JP1'

UPDATE a set sid = 14 FROM state a where a.statename='SP'

UPDATE a set sid = 15 FROM state a where a.statename='PD'

UPDATE a set sid = 44 FROM state a where a.statename='PQ'

How can we do that.

Thanks

Re: Update in Qlik script

Anil_Babu_Samineni — Mon, 06 Feb 2017 05:01:20 GMT

Look this

https://www.analyticsvidhya.com/blog/2014/09/qlikview-incremental-load/

Re: Update in Qlik script

vinieme12 — Mon, 06 Feb 2017 05:23:00 GMT

Why don't you use applymap() instead???

Don't join - use Applymap instead

I would suggest doing dml's in db environment only

Re: Update in Qlik script

Anonymous — Mon, 06 Feb 2017 05:25:05 GMT

Hi Vineeth ,

First i have create one column in existing qvd .(statename)

How to do that.

Re: Update in Qlik script

vinieme12 — Mon, 06 Feb 2017 05:28:17 GMT

Do you want to assign numeric values to state name or is it the other way around?

Create an inline table or create a table in excel and left join to your fact table

Re: Update in Qlik script

Anonymous — Mon, 06 Feb 2017 05:36:32 GMT

Yes, I want to assign state name with numerical id but I want to ask you if create inline table then on which basisi will join inline table to fact table.

Re: Update in Qlik script

vinieme12 — Mon, 06 Feb 2017 05:39:32 GMT

Don't you have the state name in your fact table??

Or other field like city that you could link with??

Re: Update in Qlik script

Anonymous — Mon, 06 Feb 2017 05:48:03 GMT

nope...

Re: Update in Qlik script

vinieme12 — Mon, 06 Feb 2017 05:53:54 GMT

Then on what field were you using the update set, when you don't have the field?

Can you list the fields in your fact?

Re: Update in Qlik script

Anonymous — Mon, 06 Feb 2017 06:05:49 GMT

ohh ! sorry vineeth I have state field in my fact table now to create state id for that with new column but in my state field there have to many state but i want to create id for few states

Re: Update in Qlik script

vinieme12 — Mon, 06 Feb 2017 06:11:00 GMT

For Example, the below is your Fact:

FACT

statename,

Field1

field2,

Field3,

From XXXXX;

Left Join

LOAD * INLINE [

statename,sid

MAH,1

MAH1,1

KAS,2

.....and so on list statenames and sid for those states which you want to assign numeric id's

];

This should resolve your problem