Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Replace existing values with New

Hi All,

Need your help guys!! 🙂

I have attached a sample Excel File & find below the Requirement.

First Sheet(R11) has 3 Rows & second Sheet(R12) has 2 Rows.

Now i want to replace the whole values of 2nd & 3rd Row from First table(i.e Sheet - R11) with 1st & 2nd Row of the Second Sheet (R12)

The Key between 2 Sheets/Table is No & New No in 2nd Sheet.

Thanks in Advance 🙂

Srini

3 Replies
erichshiino
Partner - Master
Partner - Master

Im not sure if I understood the link between the tables.

My basic idea was:

- Read first the 2nd table.

- Then, I read from first table only the IDs that I didn't have on 2nd table.

You probably are going to rename some field to actually replace values, but I was not sure which ones you needed.

Hope it helps,

Erich

Table:

LOAD ID,

     Num,

     [New No],

     Amt

FROM

[Incremental Testing Logic.xlsx]

(ooxml, embedded labels, table is R12);

Concatenate(Table)

LOAD ID,

     Num,

     Revenue

FROM

[Incremental Testing Logic.xlsx]

(ooxml, embedded labels, table is R11)

where not exists(ID)

;

Not applicable
Author

Hi Erich,

My Requirement is as below

First Sheet (R11) contains 3 Rows

Second Shet(R12) contain 2 Rows

Now the Link between the First sheet & second shett is the No (Field from first sheet) & Num (Field in the second Sheet).

Now i want a report as attached File (As given in Sheet 3).

Now one more thing if in case i keep on adding any new rows in the R12(sheet2) then these rows should keep on appending in the Report.

Please let me know if you need any further info on the same

Thanks a lot for your kind help on the same. 🙂

Srini

erichshiino
Partner - Master
Partner - Master

Hi, The script is  almost the same

I just changed some field names to match the requirement.

The logic is.

Read all sheet 2 (since if there is the same 'Num' in sheet1 it would be replaced anyway)

Read from sheet1 only the records with  'Num' that was NOT previously loaded (using not exists)

Hope it helps,

Erich

Table:

LOAD ID,

     Num,

     [New No],

     Amt

FROM

[Incremental Testing Logic.xlsx]

(ooxml, embedded labels, table is R12);

Concatenate(Table)

LOAD ID,

     Num,

     Revenue as Amt

FROM

[Incremental Testing Logic.xlsx]

(ooxml, embedded labels, table is R11)

where not exists(Num)

;