topic Re: Time Series and Parameters in QlikView

Time Series and Parameters

Anonymous — Wed, 29 Feb 2012 13:37:23 GMT

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!!!

Time Series and Parameters

CELAMBARASAN — Wed, 29 Feb 2012 13:52:18 GMT

Hi,

If the above query works in SQL then it will also work in qlikview.You can also try with SQL before select statement.

SQL Select EffectiveDate, Value1, Value2, StartDate,EndDate

From X,Y

WHERE Y.EffectiveDate = @EffectiveDate

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

Which database vendor you are using?

Celambarasan

Time Series and Parameters

jagan — Wed, 29 Feb 2012 14:01:52 GMT

Hi,

Is @EffectiveDate is a variable or Column name, if it is a column name it definitely works.

Regards,

Jagan.

Time Series and Parameters

Anonymous — Thu, 01 Mar 2012 06:22:38 GMT

Hi,

@EffectiveDate is a Parameter.

In SQL I have this query

Select EffectiveDate, Value1, Value2, StartDate,EndDate

From X,Y

WHERE Y.EffectiveDate = @EffectiveDate

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

In Qlikview I have:

LOAD *;

SQL Select EffectiveDate, Value1, Value2, StartDate,EndDate

From X,Y

I have removed the WHERE clause because I put the EffectiveDate in a listbox so I can slice the data

based on EffectiveDate, it basically mimics the WHERE Y.EffectiveDate = @EffectiveDate .

But this does not give me the correct values for the StartDate and EndDate,

I cannot find a way to mimic WHERE @EffectiveDate Between X.StartDate and X.EndDate.

Time Series and Parameters

CELAMBARASAN — Thu, 01 Mar 2012 06:41:21 GMT

Hi,

Where your going to display this(Which chart type and dimensions) and what information?

Can you give me a brief about your problem if i didn't understand clearly about your problem?

Solution to you based on my understanding

Create Straight table.

You can have

EffectiveDate, Value1, Value2, StartDate,EndDate as dimension

Expression as =if(StartDate>=Max(EffectiveDate) AND EndDate<= Max(EffectiveDate),1)

Go to Presentation Tab of the chart properties use Hide Column option for the above Expression.

Hope it helps

Celambarasan

Re: Time Series and Parameters

deepakk — Thu, 01 Mar 2012 06:47:17 GMT

hi,

You can do this in two ways.

Method 1 : Create calendar , year, month and day for the effective date and select the required days

eg select 2011 , jan and days as 1 to 10..

This will work same as effectivdate >= 01-Jan-2011 and <= 10-Jan-2011.

The disadvantage of the above case is selecting dates between two months like 15th jan to 15th Feb.

for such cases you can go by method2

Create a calendar object and use two variables inside your reports.

I have attach a sample file . Ihope that helps

Deepak

Time Series and Parameters

Anonymous — Thu, 01 Mar 2012 06:59:43 GMT

Now when I choose EffectiveDate = '31-12-2011'

I want StartDate and EndDate to automatically filter i.e StartDate < EffectiveDate and EndDate> EffectiveDate.

In this case the only possible value for StartDate is '21-12-2011' and EndDate is '03/01/2012'.

A StartDate such as '16-12-2011' is not a possibility, even though it is less than the EffectiveDate, since it's EndDate is '20-12-2011'.

Time Series and Parameters

CELAMBARASAN — Thu, 01 Mar 2012 07:11:59 GMT

Hi,

This is default QV feature it shows the possible values based on the logical links between Startdate and end Date with the Effective date.

Its like what all the startdates and end dates that matched with the effective date.

In Query

SQL Select EffectiveDate, Value1, Value2, StartDate,EndDate

From X,Y

WHERE Y.EffectiveDate = '31-12-2011'; this is what QV does it equals the selection to the same field in that way only List boxes designed.

You Can Do this by expression i think

Check with this

=Aggr(if(StartDate>=Max(EffectiveDate) AND EndDate <= Max(EffectiveDate),StartDate),StartDate,EndDate) for Start date list box.

=Aggr(if(StartDate>=Max(EffectiveDate) AND EndDate <= Max(EffectiveDate),EndDate ),StartDate,EndDate) for End date list box.

But it shows possible values only.

Celambarasan

Re: Time Series and Parameters

deepakk — Thu, 01 Mar 2012 07:18:15 GMT

hi ,

If you can attach a sample data , then it would be able to provide a work around.

Deepak

Re: Time Series and Parameters

deepakk — Thu, 01 Mar 2012 08:29:05 GMT

hi,

I am not sure but you can create the start date and end date from the script and check

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

I hope this helps.

Deepak

Re: Time Series and Parameters

Anonymous — Thu, 01 Mar 2012 08:35:28 GMT

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".

Re: Time Series and Parameters

CELAMBARASAN — Thu, 01 Mar 2012 08:43:17 GMT

Hi,

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

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

Celambarasan

Re: Time Series and Parameters

CELAMBARASAN — Thu, 01 Mar 2012 08:44:40 GMT

Hi,

Did you tried with this expression in listbox

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

Celambarasan

Re: Time Series and Parameters

Anonymous — Thu, 01 Mar 2012 08:47:46 GMT

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.

Re: Time Series and Parameters

deepakk — Thu, 01 Mar 2012 08:53:43 GMT

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;

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

Time Series and Parameters

Anonymous — Thu, 01 Mar 2012 09:10:28 GMT

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

Re: Time Series and Parameters

deepakk — Thu, 01 Mar 2012 09:45:52 GMT

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

Time Series and Parameters

Anonymous — Thu, 01 Mar 2012 10:34:48 GMT

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?

Re: Time Series and Parameters

deepakk — Thu, 01 Mar 2012 11:29:48 GMT

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

Re: Time Series and Parameters

Anonymous — Thu, 01 Mar 2012 11:43:32 GMT

Got it Working.

Thanks for all Your Help.