topic Re: how can i update a field in main table matching multiple columns with secondary tables? in QlikView

how can i update a field in main table matching multiple columns with secondary tables?

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

Hi everyone

I need help on the below case

my primary data consists of around 29 fields and about 500K rows.

For simplicity i have created an example inline table with only 4 columns

mainData:

LOAD * INLINE [Status, Country, Location, Result

Active, USA, Area1, N/A

Inactive, USA, Area2, N/A

Inactive, Canada, Area1, N/A

Active, Canada, Area2, N/A

Unknown, Italy, Area3, N/A

Active, Germany, Area5, N/A

Active, Germany, Area2, N/A

];

I have many additional tables that have 4 or less columns

modifyData1:

LOAD * INLINE [newStatus, newCountry, newLocation, newResult

Active, USA, Area1, Good

Active, USA, Area2, Bad

Active Canada, Area2, Average

Active, Germany Area2, Excellent

];

(i might have another modifyData2 table where newstatus=Inactive and so on)

I want to update the mainData table such that

if newstatus=Status AND NewCountry=Country AND newLocation=Location then

replace the N/A value with the newResult value

So after running the update my original data should look like this

Active, USA, Area1, Good

Inactive, USA, Area2, N/A

Inactive, Canada, Area1, N/A

Active, Canada, Area2, Average

Unknown, Italy, Area3, N/A

Active, Germany, Area5, N/A

Active, Germany, Area2, Excellent

(note that not necesseraly all values of modifyData1 and mainData should match)

the closes i got to this idea was mapping table and applymap but i need to match multiple fields to make the change.

Any help is greatly appreciated

thank you all

Re: how can i update a field in main table matching multiple columns with secondary tables?

neelamsaroha157 — Fri, 07 Sep 2018 20:03:12 GMT

Check this -

You can drop the extra fields at the end of the script.

Re: how can i update a field in main table matching multiple columns with secondary tables?

Anonymous — Sat, 08 Sep 2018 03:48:10 GMT

Thank you for your reply

I'm searching for a solution that does not modify the structure of the mainData table(mainData --> tmainData-1)

(Note: that my actual mainData is around 29 fields and each modifyData i have will change a specific field)

because i have around 15 modifydata tables that have to modify some field in the mainData - trying to manage all that will be a nightmare.

How can i copy the data of the tmainData1 to mainData keeping the original fields Status, Country, Location and replace the result field with the finalResult

or is there a way to keep mainData intact and just loop through the modifydata rows and update mainData(result field) accordingly

again thank you again

take care