Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
Can you post what you are expected output is?
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);
Anand, thank you for your reply. I've updated my original post with a table to illustrate the results I was hoping to emulate.
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);
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
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:-
Final file is this i am using Data file from excel with two tables.
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.
I am pleased to have helped You.
Regards,
Antonio