Solved: Update in Qlik script - Qlik Community

Anonymous · ‎2017-02-05

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

vinieme12 · ‎2017-02-06

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

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.

View solution in original post

Anil_Babu_Samineni · ‎2017-02-06

Look this

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

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful

vinieme12 · ‎2017-02-06

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

Don't join - use Applymap instead

I would suggest doing dml's in db environment only

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.

Anonymous · ‎2017-02-06

Hi Vineeth ,

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

How to do that.

vinieme12 · ‎2017-02-06

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

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.

Anonymous · ‎2017-02-06

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.

vinieme12 · ‎2017-02-06

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

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

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.

Anonymous · ‎2017-02-06

nope...

vinieme12 · ‎2017-02-06

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?

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.

Anonymous · ‎2017-02-06

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