topic Re: Changing original field value in QlikView

Changing original field value

Mon, 23 Feb 2015 15:01:25 GMT

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!

Re: Changing original field value

Anonymous — Mon, 23 Feb 2015 15:52:44 GMT

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.

Re: Changing original field value

Mon, 23 Feb 2015 16:04:51 GMT

Perfect solution. This should work.

Re: Changing original field value

Tue, 24 Feb 2015 12:28:39 GMT

it works! thank you so much!!!

Re: Changing original field value

Tue, 24 Feb 2015 14:27:18 GMT

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!

Re: Changing original field value

Anonymous — Tue, 24 Feb 2015 23:10:35 GMT

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

Re: Changing original field value

Wed, 25 Feb 2015 07:24:09 GMT

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

Re: Changing original field value

Anonymous — Wed, 25 Feb 2015 17:59:25 GMT

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.

Re: Changing original field value

Thu, 26 Feb 2015 13:56:12 GMT

Michael Solomovich

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