Time Series and Parameters

[Anonymous]

Hi,

I am basically loading data from a time series table, with the ffg in SQL:

Select EffectiveDate, Value1, Value2, StartDate,EndDate

From X,Y

WHERE  Y.EffectiveDate = @EffectiveDate

               AND @EffectiveDate Between X.StartDate and X.EndDate.

The problem is when I read into Qlikview without the, WHERE clause of cause, since I want to slice on EffectiveDate in Qlikview.

The EffectiveDate works but not the StartDate and EndDate.

Does anybody have an idea on how to get mimic this WHERE clause into Qlikview.

THANKS!!!

[Anonymous]

Is it not possible to write a SQL script as an Expression so I can filter the listbox with a SQL query.

I do not know how to create new objects based on an Expression.

If I create a new Listbox and put

Date(num(if( num(StartDate) <= num(EffectiveDate) and EndDate >=  num(EffectiveDate) ,StartDate)),'DD-MM-YYYY') as StartDate

in an expression I get an error, saying "garbage after as".

[CELAMBARASAN]

Hi,

     In list box you should use only expression not the alias.

     like

     Date(num(if( num(StartDate) <= num(EffectiveDate) and EndDate >=  num(EffectiveDate) ,StartDate)),'DD-MM-YYYY')

Celambarasan

[CELAMBARASAN]

Hi,

     Did you tried with this expression in listbox

     =Aggr(if(StartDate>=Max(EffectiveDate) AND EndDate <= Max(EffectiveDate),StartDate),StartDate,EndDate)

Celambarasan

[Anonymous]

Hi, I have tried that way and numerous others.

I get the same results.

I will have to find a work around.

Thank All of You for your Help and Assistance.

[deepakk · Accepted solution]

hi,

Just to add to the above post. You can add to the sql statement by using Load statement before Sql. It will look something similar to below one.

load

Date(num(if( num(StartDate) <= num(EffectiveDate) and EndDate >=  num(EffectiveDate) ,StartDate)),'DD-MM-YYYY') as StartDate,

Date(num(if( num(StartDate) <= num(EffectiveDate) and EndDate >=  num(EffectiveDate) ,EndDate)),'DD-MM-YYYY')

as EndDate,

EffectiveDate, Value1, Value2;

select * from XY;

or

Use the resident to create an instance of sql table and use the logic.

Select * from X;

Load

Date(num(if( num(StartDate) <= num(EffectiveDate) and EndDate >=  num(EffectiveDate) ,StartDate)),'DD-MM-YYYY') as StartDate,

Date(num(if( num(StartDate) <= num(EffectiveDate) and EndDate >=  num(EffectiveDate) ,EndDate)),'DD-MM-YYYY')

as EndDate,

resident X;

Deepak

[Anonymous]

EffectiveDate will be changing all the time depending on your selection so I cannot Reload different StartDates and EndDates when EffectiveDate changes.

[deepakk]

hi,

I think you understood me wrongly.  When you dod it at script. Each effective date is getting linked to start date based on the logic. Hence when you make a selection in the front end it will display you the respective result.

The logic is same if you do it front end or back end. The advantage of doing at back end is the time request to process the data will be reduce hence it will much faster.

Deepak

[Anonymous]

Thanks that helps alot, it filters out correctly.

Now...

How do I select automatically all available values in a Field. I want to select all StartDates values, as I click on an EffectiveDate?

[deepakk]

hi,

Why do u wan to select it  ? Selecting eefective date means you are selecting the respective start date and end date.

I dont think there is any need to select Start date or end date manually.!!!

Deepak

[Anonymous]

Got it Working.

Thanks for all Your Help.