Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
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
erich_shiino
Honored Contributor

Re: Replace existing values with New

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

Re: Replace existing values with New

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

erich_shiino
Honored Contributor

Re: Replace existing values with New

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)

;

Community Browser