Updating QVD with excel

[didierodayo]

Hi, I need some help.

I have a QVD content below and I would like to update the QVD with a new column called STATUS. the STATUS field will be left joined  but will only update certain TEST_IDs.

1- How can I set it so that instead of NULL on the other TEST_ID STATUS column I can have NOT_VALID instead?

NOTE: this is a large QVD and new TEST_IDs will be loaded to the QVD every month so I want the new TEST_IDs to have STATUS as NOT_VALID once the reload takes place.

Thanks

QVD CONTENT:

TEST_ID	PRICE
1259	600
1260	200
1261	150
1262	222
1263	325
1264	621
1265	200
1266	100
1267	850

EXCEL CONTENT FOR UPDATE:

TEST_ID	STATUS
1261	CURRENT
1262	CURRENT
1263	CURRENT
1264	CURRENT

[MindaugasBacius · Accepted solution]

map_status:
mapping LOAD TEST_ID,
     STATUS
FROM

(ooxml, embedded labels, table is Sheet2);


LOAD TEST_ID,
     PRICE,
     ApplyMap('map_status', TEST_ID, 'NOT_VALID') as STATUS
FROM

(ooxml, embedded labels, table is Sheet1);

Result:

Also, at the end of the script just store everything back.

[didierodayo]

Thanks Mindaugas,

coud you help me with this as well please?

Updating 2 table from a 3rd table