Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
Save the Date: QlikWorld Online, June 24-25, 2020. Free global virtual event for data integration and data analytic gurus. Register Today
Highlighted
MVP
MVP

Re: Max Date minus 1 month Data

I am not against reducing data at script end. In fact, this is the right way of handling data so that we need only those records for which we need to do analysis. This will also increase performance due to having lower sized QVW.

But we don't know the size of Future QVD. Say the business grown and now QVD size is in Millions. In that case, I always prefer to use Optimized Load.

You can do something like below to convert that unoptimised load to optimized.

Let vStartDate = Num(YearStart(Today()));

Let vEndDate = Num(MonthEnd(Today(),-2));


TempDates:

Load

Date($(vStartDate) + IterNo() - 1) as TempDate

AutoGenerate 1

While $(vStartDate) + IterNo() - 1 <= $(vEndDate);


YourFactTable:

Load * From YourQVD Where Exists(TempDate,OrderDate);


Drop Table TempDates;

Let vStartDate = Null();

Let vEndDate = Null();

This way you can reduce date at back end and also take advantage of Optimised QVD Load.

Highlighted
Esteemed Contributor

Re: Max Date minus 1 month Data

the secret here is only the use of the "where exist" Right ? mrkachhiaimp

Highlighted
MVP
MVP

Re: Max Date minus 1 month Data

Yes.. QVD load only allow Alias the field and Where Exists to keep the load optimised.

Highlighted
Contributor III

Re: Max Date minus 1 month Data

Nice trick to achieve optimize load.

But I don't know somehow for me it is not digest-able that to achieve optimize load we have to maintain another temp table.

Leave it that topic, it is always most confusing for me.

What I think when

1: we are focusing on reducing reload time then we go for optimize load.

2: we required better front end user performance we go for unoptimize load or move such condition in extractor level so that my qvd become optimize.

Regards,

max

Highlighted
Contributor III

Re: Max Date minus 1 month Data

and another point which i know about where exist() is

If both field use in exist() having same name then only it will become optimize load else it will not.

so in your example it will still not gave you optimize load.

Regards,

max

Highlighted
Esteemed Contributor

Re: Max Date minus 1 month Data

Thanks mrkachhiaimp

Highlighted
Esteemed Contributor

Re: Max Date minus 1 month Data

This also sounds familiar to me...

Highlighted
MVP
MVP

Re: Max Date minus 1 month Data

Agree but in this case, we just have to rename !!