topic Re: Replacing Old Values With New Values in QlikView

Replacing Old Values With New Values

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Good Morning Community,

I have a question I hope I could get help with.

I've attached a sample of the situation I have to better illustrate my problem.

I have 2 Tables from our database. One table is a list of tasks and information related to those tasks (who it's assigned to, when it's due, etc.) . If changes are made to the fields on that task table it's recorded in a separate table. The separate Table records the Task Key, which field was changed and what the new value should be. I'm trying to replace the old information on the list of tasks with the new information from the task changes table. Any help here would be appreciated. Thank you!

*I've updated the document, it now contains a table with the expected results.

Re: Replacing Old Values With New Values

vishsaggi — Mon, 25 Sep 2017 14:12:00 GMT

Can you post what you are expected output is?

Re: Replacing Old Values With New Values

its_anandrjs — Mon, 25 Sep 2017 14:16:14 GMT

Try this way with mapping load with update new record in new column

NewTaskChanges:

Mapping

LOAD

TaskID,

// Field,

NewValue

FROM

(ooxml, embedded labels, table is Sheet2);

TaskList:

LOAD

TaskID,

Assigned,

ApplyMap('NewTaskChanges',TaskID,'NewValue') as NewAssigned,

Task,

Date

FROM

(ooxml, embedded labels, table is Sheet1);

Re: Replacing Old Values With New Values

Anonymous — Mon, 25 Sep 2017 15:09:37 GMT

Anand, thank you for your reply. I've updated my original post with a table to illustrate the results I was hoping to emulate.

Re: Replacing Old Values With New Values

Colin-Albert — Mon, 25 Sep 2017 15:34:17 GMT

You will need to create a mapping table that maps both changes using a key that includes the fieldname being changesd, and then uses the mapping table to either load the changes or original values.

You could extend this logic to change the Task data as well.

TaskChangesMap:

Mapping LOAD

TaskID & '|' & Field,

NewValue

FROM

(ooxml, embedded labels, table is Sheet2) ;

TaskList:

LOAD

TaskID,

ApplyMap('TaskChangesMap',TaskID & '|Assigned', Assigned) as Assigned,

Task,

ApplyMap('TaskChangesMap',TaskID & '|Date', Date) as Date

FROM

(ooxml, embedded labels, table is Sheet1);

Re: Replacing Old Values With New Values

antoniotiman — Mon, 25 Sep 2017 16:14:09 GMT

Hi Josh,

may be this

MasterTable:
LOAD TaskID,
Assigned as OldAssigned,
Task,
Date as OldDate
FROM
[MasterTable.xls]
(biff, embedded labels, table is Sheet1$);

Table:
Generic
LOAD TaskID,Field,NewValue
FROM
[Changes.xls]
(biff, embedded labels, table is Sheet1$);

Join(MasterTable)
LOAD * Resident Table.Assigned;
Drop Table Table.Assigned;

Join(MasterTable)
LOAD * Resident Table.Date;
Drop Table Table.Date;

LOAD TaskID,Task,
If(IsNull(Assigned),OldAssigned,Assigned) as Assigned,
Date(If(IsNull(Date),OldDate,Date)) as Date
Resident MasterTable;
Drop Table MasterTable;

Regards,

Antonio

Re: Replacing Old Values With New Values

its_anandrjs — Mon, 25 Sep 2017 16:49:07 GMT

Thanks for input try this way to load the table with two mappings used its all in the load script and result you get in the table it self

LOAD TaskID as TID,

Field as FID,

NewValue as NVAL

FROM

Data.xls

(biff, embedded labels, table is [Task_Change$]);

MapTab:

Mapping

LOAD TaskID,

// Field,

NewValue

FROM

Data.xls

(biff, embedded labels, table is [Task_Change$]);

Data:

LOAD TaskID,

Assigned,

Task,

Date,ApplyMap('MapTab',TaskID,Assigned) as ExpextedAssinTo

FROM

Data.xls

(biff, embedded labels, table is [Master_Task$]);

Final:

LOAD *,if(IsNum(ExpextedAssinTo),1,0) as Details Resident Data;

DROP Table Data;

NoConcatenate

LOAD TaskID as ExpectdId,Assigned,Task,Date as ExpectedDate,Details,ExpextedAssinTo Resident Final Where Details = 0;

LOAD TaskID as ExpectdId,Assigned,Task,ApplyMap('MapTab',TaskID,'ExpextedAssinTo') as ExpectedDate,Details,Assigned as ExpextedAssinTo Resident Final Where Details = 1;

DROP Table Final;

OutPut:-

Re: Replacing Old Values With New Values

its_anandrjs — Mon, 25 Sep 2017 16:56:47 GMT

Final file is this i am using Data file from excel with two tables.

Re: Replacing Old Values With New Values

Anonymous — Wed, 27 Sep 2017 19:41:48 GMT

Thank you very much. I had to make a few tweaks as my problem was slightly larger than the sample I provided. However the trick here was the GENERIC Load in combination with the JOIN and IF(ISNULL()) statements. Much appreciated and I got to understand a practical use case for GENERIC Load! Thanks a million guys.

Re: Replacing Old Values With New Values

antoniotiman — Wed, 27 Sep 2017 22:06:30 GMT

I am pleased to have helped You.

Regards,

Antonio