topic Re: Convert If condition into set Analysis in QlikView

Convert If condition into set Analysis

ahmar811 — Wed, 05 Jul 2017 07:31:58 GMT

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

Re: Convert If condition into set Analysis

Anil_Babu_Samineni — Wed, 05 Jul 2017 07:49:45 GMT

Please post expression?

Re: Convert If condition into set Analysis

ahaahaaha — Wed, 05 Jul 2017 07:56:30 GMT

Hi Ahmar,

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

Regards,

Andrey

Re: Convert If condition into set Analysis

Anonymous — Wed, 05 Jul 2017 08:14:57 GMT

could you add your data.xls

Re: Convert If condition into set Analysis

ahmar811 — Wed, 05 Jul 2017 08:23:06 GMT

yes you correct..

Re: Convert If condition into set Analysis

ahmar811 — Wed, 05 Jul 2017 08:24:10 GMT

Robin

Please find attached data

Regards

Ahmar

Re: Convert If condition into set Analysis

Anonymous — Wed, 05 Jul 2017 08:44:59 GMT

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;

Re: Convert If condition into set Analysis

ahaahaaha — Wed, 05 Jul 2017 09:08:08 GMT

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?

Re: Convert If condition into set Analysis

Anonymous — Wed, 05 Jul 2017 09:18:19 GMT

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.

Re: Convert If condition into set Analysis

ahmar811 — Fri, 07 Jul 2017 04:35:33 GMT

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

Re: Convert If condition into set Analysis

Anonymous — Fri, 07 Jul 2017 07:10:38 GMT

I think there's no alternative solution because you need a comparison row by row from 2 different tables.

So if you don't join, then you have to use

sum(if(...

Perhaps there's a better approach than building a carthesian product (direct or indirect) for solving your problem...