topic Re: Re: Expression for Start end date - SET Analysis in QlikView

Expression for Start end date - SET Analysis

atafsson — Mon, 17 Mar 2014 09:30:16 GMT

Hi all!

I need help to create a SET-analysis expression instead of my if-statement.

I have this expression:

if( Startdate <= date(Month,'YYYYMM') xor date(Month,'YYYYMM') >= Enddate,sum(Amount))

The expression gives the correct result but it takes very long time to calculate it for Qlikview. I have around 40 thousands of ID's with startdates from 2014-01-01 to 2039-12-31 it gives me many rows to generate the dates in the script. Every ServiceId has a uniqe start and enddate. I've also created i report structure so the serviceId's occurs on multiple places.

My Calendar is a data island.

What I would like to do is to create i SET-expression of my expression instead of the if-statement.

I attached a sample-file with a example (not for the report structure).

/AG

Re: Expression for Start end date - SET Analysis

CELAMBARASAN — Mon, 17 Mar 2014 09:43:47 GMT

Island data cannot be linked by using set analysis.

Why cannot you link those 2 tables?

Re: Expression for Start end date - SET Analysis

atafsson — Mon, 17 Mar 2014 10:04:03 GMT

Hi,

I'd like to, but I don't know how to create the link between them because of the start and enddate.

How should the the link to the calendar table take both start- and end-date into account?

How will I generate the dates in the chart between start- and end-date?

Regards

Axel

Re: Expression for Start end date - SET Analysis

CELAMBARASAN — Mon, 17 Mar 2014 10:16:18 GMT

I changed this part of script

Calendar:

LOAD

Year(addmonths($(vMin),IterNo()-1)) as %Year

,MonthStart(addmonths($(vMin),IterNo()-1)) as Month

,Year(addmonths($(vMin),IterNo()-1)) & num(month(MonthStart(addmonths($(vMin),IterNo()-1))),'00') as YearMonth

,Date($(vMin)+(Iterno()-1),'YYYY-MM-DD') as Date

,month(MonthStart(addmonths($(vMin),IterNo()-1))) as Month2

AutoGenerate(1)

//while addmonths($(vMin),IterNo()-1) <= $(vMax) ;

While Date($(vMin)+(Iterno()-1)) <=Date($(vMax));

drop table Temp;

LinkTable:

LOAD

ServiceId,

Date((Startdate + IterNo() - 1), 'YYYY-MM-DD') AS Date

Resident Tmp

While (Startdate + IterNo() - 1) <= Enddate;

And Use Expression as simply Sum(Amount)

Regards,

Celambarasan

Edit:

There was a issue calendar generation, use the below script

Calendar:

LOAD

Date,

Year(Date) as %Year

,MonthStart(Date) as Month

,Num#(Text(Date(Date, 'YYYYMM'))) as YearMonth

,month(MonthStart(Date)) as Month2;

LOAD

Date($(vMin)+(Iterno()-1),'YYYY-MM-DD') as Date

AutoGenerate(1)

//while addmonths($(vMin),IterNo()-1) <= $(vMax) ;

While Date($(vMin)+(Iterno()-1)) <=Date($(vMax));

drop table Temp;

LinkTable:

LOAD

ServiceId,

Date((Startdate + IterNo() - 1), 'YYYY-MM-DD') AS Date

Resident Tmp

While (Startdate + IterNo() - 1) <= Enddate;

Regards,

Celambarasan

Re: Re: Expression for Start end date - SET Analysis

CELAMBARASAN — Mon, 17 Mar 2014 10:45:21 GMT

Attachment would help

Re: Expression for Start end date - SET Analysis

atafsson — Mon, 17 Mar 2014 12:37:43 GMT

The problem is that with the LinkTable the script needs to generate all dates from start to end for each ServiceId.

In my real application it counted up to around 200 million rows in the LinkTable.

Is there an other way?