topic Re: Update some fields in a table in QlikView

Update some fields in a table

Mon, 22 Aug 2011 09:52:08 GMT

Hello,

Having searched the documentation and thes forums for the past days, I havent found a solution yet.

I have a huge table (Table 1) containing lots of information (1 million rows) with numerous fields A (he key) ,B,C,D,E,F ....

I have a second table (Table 2) that contains only fields A, C and D and 50000 fields

This second table contains the right value for C and D for some of the records of table 1.

So what I am trying to do is Load Table 1, and then Update fields C and D with the information stored in Table 2, without changin the information stored in fields B,E, F, ....

Example :

Table 1:

A	B	C	D	E	F	G
100	John	some text	70	Not to be changed	Not to be changed	Not to be changed
101	Marry	some text	50	Not to be changed	Not to be changed	Not to be changed
102	Bill	some text	30	Not to be changed	Not to be changed	Not to be changed
103	Sandra	some text	20	Not to be changed	Not to be changed	Not to be changed
104	Tony	some text	80	Not to be changed	Not to be changed	Not to be changed

Table 2:

A	C	D
100	New info	50
102	New info	80
103	New info	60

the result should be Table 3:

A	B	C	D	E	F	G
100	John	New info	50	Not to be changed	Not to be changed	Not to be changed
101	Marry	Some text	50	Not to be changed	Not to be changed	Not to be changed
102	Bill	New info	80	Not to be changed	Not to be changed	Not to be changed
103	Sandra	New info	60	Not to be changed	Not to be changed	Not to be changed
104	Tony	Some Text	80	Not to be changed	Not to be changed	Not to be changed

I struggled on this one but I cannot acheive any result (I was just able to fully update the lines but not partially)

Thanks in advance for any advice.

Re: Update some fields in a table

Miguel_Angel_Baeyens — Mon, 22 Aug 2011 10:05:33 GMT

Hello,

At a first glance, I'd make use of the ApplyMap() function, because there is a key field we can use in all tables. The script will be a three step load (first map, second map, full table) and the result will be one table with updated values. According to the info you shown above, the script looks like this

MappingTable1:

MAPPING LOAD * INLINE [

A, C

100, New info

102, New info

103, New info

];

MappingTable2:

MAPPING LOAD * INLINE [

A, D

100, 50

102, 80

103, 60

];

UpdatedTable:

LOAD A,

ApplyMap('MappingTable1', A, C) AS C, // updates according to MappingTable1, if there is no value within returns original C

ApplyMap('MappingTable2', A, D) AS D, // similar to the above with the other table

INLINE [

A, B, C, D, E, F, G

100, John, some text, 70, Not to be changed, Not to be changed, Not to be changed

101, Marry, some text, 50, Not to be changed, Not to be changed, Not to be changed

102, Bill, some text, 30, Not to be changed, Not to be changed, Not to be changed

103, Sandra, some text, 20, Not to be changed, Not to be changed, Not to be changed

104, Tony, some text, 80, Not to be changed, Not to be changed, Not to be changed

];

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

Re: Update some fields in a table

Tue, 23 Aug 2011 15:46:47 GMT

Thank you very much !

I am going to try this.