if and match condition of 2 fields in qliksense

[richard24best]

Hi All,

How do i write in QS Data load editor - 

if(match("L2 Driver (M)",'Client Reports') and (isnull("Manual Process - Client ()")) or "Manual Process - Client ()"='' or "Manual Process - Client ()"='-','Manual Process - Client ()') as Exception

When iam using the above all the 3 rows are showing with msge 'Manual Process - Client ()' inspite of L2 Driver value is not Client Reports.

Not sure if i am missing something or doing it incorrectly.

Appreciate any best suggestion

Regards,

Richard

[BrunPierre]

I'm failing to understand the logic of your condition. Could you provide more clarification?

[rwunderlich · Accepted solution]

You need parens around the entire AND group:

if(match("L2 Driver (M)",'Client Reports') and
(
isnull("Manual Process - Client ()") or "Manual Process - Client ()"='' or "Manual Process - Client ()"='-','Manual Process - Client ()'
)
)as Exception

[richard24best]

Hi,

For more clarification -

I have list of fields under L2 driver - client reports , system,function,etc

So I want to write logic saying whenever L2 driver is entered as client reports then "Manual Process - Client ()" should not be null or '-' as exception1

Similarly for 2nd scenerio -

I have list of fields for L1 driver as client,product etc and L2 driver - client reports , system,function,etc

I want to write whenever L1 driver is client and L2 driver is client reports then Manual Process - Client ()" should not be null or '-' as exception2

[BrunPierre]

Like this?

If("L2 Driver (M)" = 'Client Reports' and not IsNull(EmptyIsNull("Manual Process - Client ()")), 'Manual Process - Client ()', Null()) as exception1

If("L1 Driver (M)" = 'client' and "L2 Driver (M)" = 'Client Reports' and not IsNull(EmptyIsNull("Manual Process - Client ()")), 'Manual Process - Client ()', Null()) as exception2

[richard24best]

Hi BrunPierre,

Thanks for your prompt support.

Its still not showing records as expected. Attached is the excel sample of data. 

Process Reference	L1 Driver (M)	L2 Driver (M)	Report Name	Manual Process - Client ()
123	Data	Client Bespoke	Report 1	-
456	Client	Client Bespoke	Report 2	-
785	Client	Client Reports	-	ABC Limited

So the requirement is that When L1 Driver='Client' then [Manual Process - Client() should have some values if not as Exception1

Similarly, when L1 Driver = Client and L2 Driver= Client Report then Report name field should have some values if not as Exception2

Appreciate your time and support in advance.

Regards,
Richard

[richard24best]

To add on the result expected is that it should -

1 Exception1 for Process reference 456 as L1 Driver=Client but [Manual Process - Client()] is missing

1 Exception2 for Process reference 785 as L1 Driver=Client and L2=Client Reports but Report Name is missing

[richard24best]

Thank you for your support.

Small changes as per your suggestion worked fine

If("L1 Driver (M)" = 'Client' and [Manual Process - Client ()]='-' or [Manual Process - Client ()]=' ', 'Manual Process - Client ()') as Exception1,
If("L2 Driver (M)" = 'Client Reports' and [Report Name]='-' or [Report Name]='', 'Report Name') as Exception2

[jiana02]

Thanks