topic Re: Creating New Fields Through Logic in QlikView

Creating New Fields Through Logic

rsdhavle — Wed, 21 Jan 2015 12:40:07 GMT

I have two fields RegionA and RegionB. I want to introduce a new field which will have logic like.

RegionA = 'ASIA' OR RegionB ='ASIA' ..I want this logic in new field lets say named RegionC. Will the above logic work for my scenario??

Re: Creating New Fields Through Logic

Josh_Good — Wed, 21 Jan 2015 14:13:06 GMT

Hi Rohan,

Yes this sort of logic will work. In your load script you can write the logic using an if statement. I created a small example below. The exact expression will depend on how you want to deal with other members in the RegionA or RegionB fields. If you have a more complex logic (e.g. different outcomes for RegionC based on several combinations of RegionA and RegionB you may want to concatenate the two fields and then use a mapping table to resolve the logic.

-Josh

QLik

Load

....

If(RegionA='ASIA' or RegionB='ASIA', 'ASIA', RegionA) as RegionC,

....

;

Re: Creating New Fields Through Logic

avinashelite — Wed, 21 Jan 2015 14:17:53 GMT

Hi Rohan,

Did you issue got resolved? if not please post your app.

Re: Creating New Fields Through Logic

kuba_michalik — Wed, 21 Jan 2015 14:17:55 GMT

Depends on the data model and what you want to achieve. If the new field is supposed to be in a table which already has RegionA and RegionB, then sure. If RegionA and RegionB are in different tables in the data model, then no, unless you match them in some way.

Re: Creating New Fields Through Logic

rsdhavle — Thu, 22 Jan 2015 07:13:16 GMT

Both fields are in the same table. We want the scenario like Region A = 'ASIA' (Region B Can be asia or anything else)

OR RegionB = 'ASIA'(Region A can be asia or anything else) So its simple OR condition but usually Qlikview shows it like AND and OR logic does not work

RegionA RegionB Revenue

ASIA UK 100

ASIA ASIA 200

UK UK 300

US ASIA 100

So in above case when i will select my newly created field Region C which has logic like Region A = Asia or Region B = Asia The revenue for it should come as 400. I am trying to achieve it

Re: Creating New Fields Through Logic

anbu1984 — Thu, 22 Jan 2015 08:33:32 GMT

Sum({<Region A = {'Asia'}> + <Region B = {'Asia'}>}Revenue)

Re: Creating New Fields Through Logic

kuba_michalik — Thu, 22 Jan 2015 08:35:31 GMT

If it's just about Asia, then no problem. As already suggested, create new field in script like this:

If(RegionA='ASIA' or RegionB='ASIA', 'ASIA', <whatever it's to be when neither is ASIA>) as RegionC

But you can't store "logic" in a field, just the result of applying it to other fields. So, if you wanted to switch to selecting "RegionA='UK' OR RegionB='UK'" at a click, you can't do this that way. For that you have to either incorporate set analysis into your expression, or create the new field as linked to all combinations of RegionA and RegionB which satisfy the logic for each and every region. I'd stick with set analysis if I were you.

Here's an example of general implementation of OR-mode selection: Or-mode selections in QlikView The Qlik Fix! . If you need it for just one field, you can trim it down to something much simpler.

Re: Creating New Fields Through Logic

Anonymous — Thu, 22 Jan 2015 08:39:18 GMT

Try like this

If(RegionA='ASIA' or RegionB='ASIA', 'ASIA', RegionA) as RegionC,

Re: Creating New Fields Through Logic

rsdhavle — Thu, 22 Jan 2015 09:11:58 GMT

does not work

Re: Creating New Fields Through Logic

jagan — Thu, 22 Jan 2015 09:19:16 GMT

Hi,

Try like this

LOAD

If(RegionA='ASIA' OR RegionB='ASIA', 1, 0) AS Flag

FROM DataSource;

Now use this expression

=Sum({<Flag={1}>} Revenue)

Or if you want to handle this front end try this expression

Sum({<Region A = {'ASIA'}> + <Region B = {'ASIA'}>}Revenue)

Hope this helps you.

Regards,

Jagan.

Re: Creating New Fields Through Logic

Anonymous — Thu, 22 Jan 2015 09:25:24 GMT

try like

Tab2:
LOAD * INLINE [
RegionA,RegionB,Revenue

ASIA,UK,100

ASIA,ASIA,200

UK, UK,300

US, ASIA,100
];

Load
RegionA,
RegionB,
If(RegionA='ASIA' or RegionB='ASIA','ASIA') as RegionC
Resident Tab2;

Re: Creating New Fields Through Logic

rsdhavle — Thu, 22 Jan 2015 10:23:57 GMT

This was helpful. I used this expression and in the region A and region b i passed variable based on RegionC field

RegionC field i created it in inline table so that i can use it as values in variables.

Thanks

Re: Creating New Fields Through Logic

rsdhavle — Tue, 27 Jan 2015 12:01:16 GMT

Can you tell me now this L2 + L4 condition i have to take in section access how can i take that? As if i pass L2 = Asia ,L4 = asia in section access table, it works as AND