Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I have the following table:
Field1 | Field2
a tr125.415
a gf768.989
b zk098.988
And other table
Field1 | Rule
a left(Field2,3)
b | right(Field2,5)
The second table is an excel file that can be updated.
What i want: Load a table where transforms Rule field into Qlik Script language and the end result should be:
Field1 | Field2 | Result
a tr125.415 tr1
a gf768.989 gf7
b zk098.988 8.988
Can anyone awnser and help me with this?
thanks in advance
Best Regards
Bruno Paulo
Load Field1,
Field2,
if (Field1 = 'a', left(Field2,3),
if (Field2 = 'b', right(Field2,5))) as Result
From ...
This just has your rules hard coded in. hopefully that is ok.
Yes but i want to make the rules dynamic. So i can put them on the code.
see attached qvw!
hope this helps
This solution is ok, but i still have a problem. If a user add a rule the code won't work.
added rule for field c
Bruno, did Frank's last post get you what you needed? If so, please be sure to give him proper credit by clicking the Accept as Solution button on his post, so others will know it did solve the issue completely. If not, please update, and if you figured out something else, please post what you did and mark that as the solution.
Regards,
Brett
One solution is.
Rules:
LOAD RowNo() As RowID,* INLINE [
Field1, Rule
a, "Left(Field2,3)"
b, "Right(Field2,5)"
];
tmpR:
LOAD Concat(Chr(39)&Field1&Chr(39),',',RowID) As F1Str,
Concat(Rule,',',RowID) As RuStr
Resident Rules;
Let vF1=Peek('F1Str');
Let vRu=Peek('RuStr');
tab1:
LOAD *, Pick(Match(Field1,$(vF1)),$(vRu)) As Result;
LOAD * INLINE [
Field1, Field2
a, tr125.415
a, gf768.989
b, zk098.988
];
Drop Table Rules, tmpR;