Replacing Old Values With New Values

[Anonymous]

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.

[vishsaggi]

Can you post what you are expected output is?

[its_anandrjs]

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

[Anonymous]

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

[Colin-Albert]

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

[antoniotiman · Accepted solution]

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

[its_anandrjs]

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:-

[its_anandrjs]

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

[Anonymous]

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.

[antoniotiman]

I am pleased to have helped You.

Regards,

Antonio