topic Re: Replace existing values with New in QlikView

Replace existing values with New

Wed, 25 May 2011 08:59:46 GMT

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

Re: Replace existing values with New

erichshiino — Wed, 25 May 2011 13:36:33 GMT

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)
;

Re: Replace existing values with New

Wed, 25 May 2011 16:47:55 GMT

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

Re: Replace existing values with New

erichshiino — Wed, 25 May 2011 19:03:15 GMT

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)
;