Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Announcing the newest addition to the Qlik Community, Qlik Gallery! Learn More
ahmar811
Contributor III

Convert If condition into set Analysis

Hi

Experts

Need your help to solve this problem

I have If condition in my expression I am trying to convert into Set Analysis but its not working

see my attached file.

Regards

Ahmar

10 Replies

Re: Convert If condition into set Analysis

Please post expression?

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
ahaahaaha
Honored Contributor

Re: Convert If condition into set Analysis

Hi Ahmar,

Is it correct that in the chart you use the data of two unrelated tables?

Regards,

Andrey

robin_hausdoerfer
Valued Contributor III

Re: Convert If condition into set Analysis

could you add your data.xls

ahmar811
Contributor III

Re: Convert If condition into set Analysis

yes you correct..

ahmar811
Contributor III

Re: Convert If condition into set Analysis

hi

Robin

Please find attached data

Regards

Ahmar

robin_hausdoerfer
Valued Contributor III

Re: Convert If condition into set Analysis

I think that won't work as you tried it because you have to compare it row by row...

btw. there are null() values within your comparision, I'm not sure, if you thought about it...

--------------------------------------

With the help of an outer join + flag, it is possible:

TEMP:
LOAD Date,
Sum
FROM
Data.xlsx
(
ooxml, embedded labels, table is Sheet1);

outer join

LOAD Unlink_FiscalMonth,
Unlink_FiscalYR,
Unlink_Date,
month(Unlink_Date) as Month_Temp,
Year(Unlink_Date) as Year_Temp,
Date(Date#(Unlink_Month_End,'MM/DD/YYYY'),'DD-MM-YYYY') as Unlink_Month_End
FROM
Data.xlsx
(
ooxml, embedded labels, table is Sheet2);

FINAL:
LOAD
*,
if (Date>=Unlink_Month_End,1) as FLAG
Resident TEMP;
DROP Table TEMP;

ahaahaaha
Honored Contributor

Re: Convert If condition into set Analysis

Excuse me, but I can not understand what information your table contains with condition IF? This table is a union of many-to-many two unrelated source tables, and plus with the condition IF. What kind of task is solved in general?

robin_hausdoerfer
Valued Contributor III

Re: Convert If condition into set Analysis

hm, he builds a carthesian product due to no matching fields.

So when he calculates

sum(if(FIELDFROMTABLE1=...,FIELDFROMTABE2))

he does an indirect outer join

I'm also not sure, if that is desired at all.

ahmar811
Contributor III

Re: Convert If condition into set Analysis

Hi

Robin

I appreciate

But there is one problem with this code when I apply above code with large amount of data it take lots of memory and processing 

Regards

Ahmar