Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!!!
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
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
Hi,
Is @EffectiveDate is a variable or Column name, if it is a column name it definitely works.
Regards,
Jagan.
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.
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
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
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'.
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
hi ,
If you can attach a sample data , then it would be able to provide a work around.
Deepak
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