Convert If condition into set Analysis

[ahmar811]

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

[Anil_Babu_Samineni]

Please post expression?

[ahaahaaha]

Hi Ahmar,

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

Regards,

Andrey

[Anonymous]

could you add your data.xls

[ahmar811]

yes you correct..

[ahmar811]

hi

Robin

Please find attached data

Regards

Ahmar

[Anonymous]

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]

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?

[Anonymous]

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]

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