Max Date minus 1 month Data

[abhaytrip]

Hi guys ,

Im facing an issue where I have Date in the qvd from 1/1/2017 to 3/1/2018 in the format MM/DD/YYYY.

but in qlikview I need to show Data only till Feb , 2018 that is (2/1/2018).

from 1/1/2017 to 2/1/2018

So thats basically excluding the previous month data i need to represent it considering that im in the month of April now.

What ways is this possible for me to go ahead with ?

Thanks

[MK_QSL]

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.

[YoussefBelloum]

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

[MK_QSL]

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

[big_dreams]

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

[big_dreams]

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

[YoussefBelloum]

Thanks mrkachhiaimp‌

[YoussefBelloum]

This also sounds familiar to me...

[MK_QSL]

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