Changing original field value

[#1]

Hi All

I need to implement the following thing:

lets say I have a table called activities and it's look like this in qv (an hierarchy table):

Activity ID	Activity name	activity result	activity containing session id	containing session name
1	FP	success	null	null
2	F	error	1	FP
3	R	Success	1	FP
4	RP	Error	null	null
5	LP	Error	NUll	null
6	Wi	success	5	LP
7	LP	error	null	null

I want to change the "activity result" field to display the activity result of his "mandatory" child. example:

Activity ID 1 has 'success' activity result. it's child (activity id 2) has activity result 'error'. I want to change the activity id 1 result to be also 'error' like it's child. Activity ID 1 has more then one child, so I have another table with the "critical" child, the one that should affect his parent.

this is the table (inline):

Session name	critical child
FP	F
RP	R
LP	Wi

if the session name doesn't have children, the original result will stay the same.

the final table should look like this:

Activity ID	Activity name	activity result	activity containing session id	containing session name
1	FP	error	null	null
2	F	error	1	FP
3	R	Success	1	FP
4	RP	Error	null	null
5	LP	success	NUll	null
6	Wi	success	5	LP
7	LP	error	null	null

Can someone please advise?

I hope I was clear enough. Thank you all!

[Anonymous]

Try this:

First, when you load the first table, take care about the capitalization to make your life easier.  That is, for example
lower("activity result") as "activity result"
to conver 'Success' to 'success' and 'Errror' to 'error'.
Next, create two maps:

Parent2ChildMap:
MAPPING LOAD DISTINCT
"Session name",
"critical child"
RESIDENT ParentChildMap;  // I just inventing the table name here

Child2ResultMap:
MAPPING LOAD DISTINCT
"Activity name",
"activity result"
RESIDENT Data   // where "Data" is the name of the original table
WHERE match("Activity name", 'F','R','Wi');

So, the result will be

Result:
NOCONCATENATE
LOAD
...
if("activity result"='success', applymap('Child2ResultMap',applymap('Parent2ChildMap', "Activity name")), 'error') as "activity result",
...
RESIDENT Data;

DROP TABLE Data;

Hope it helps.

[#3]

Perfect solution. This should work.

[#4]

it works! thank you so much!!!

[#5]

Michael Solomovich

Hi,

Thanks for answering. I have a small issue with the solution. the problem is that the "Activity ID" is the unique identifier of each row. in the solution above, the result of one activity will affect all others activities with the same activity name.

I'll really appreciate if you take a look at the attached test file

thank you so much!

[Anonymous]

First, replace the code with applymaps with this one:

if(match(Activitiy_name,'FP','RP','LP'), ApplyMap('Child2ResultMap',applymap('Parent2ChildMap',Activitiy_name)), Activitiy_result) as Activitiy_result,

Second, from your data looks like parent-child relation is not as straightforward as it looked initially. 
So, it is not a small problem.  How do you know what child belong to what parent?

PS: I'm very busy these days, so my replies could take time...

[#7]

Hi

Thanks for answering, sorry I wasn't clear enough!

the "activity containing session id" is the field that represent the parent of each row. lets say Activity ID 2 has "containing session id"=1, and also "Activity ID" 3 has "containing session id"=1 so both are the children of Activity ID 1. but according to Parent2ChildMap the mandatory child of Activity ID 1 ("FP") is "F" so only Activity ID 2 is the relevant child the should affect Activity ID 1 result.

Hope now I was clear.

thank you

[Anonymous · Accepted solution]

Got time for this while running a long reload...

I changed definition of parent and child, including activity and activity containing session id respectively in each mapping.  See attached.

[#9]

Michael Solomovich

WOW!!! absolutely amazing! perfectly works! thank you so much!