19 Replies Latest reply: Mar 1, 2012 6:47 AM by Abdur Razak Amod RSS

    Time Series and Parameters

      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 Adhimulam

          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 mohan rao appala

            Hi,

             

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

             

            Regards,

            Jagan.

            • Time Series and Parameters

              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 Adhimulam

                  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
                    Deepak Kurup

                    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

                    Case 1.PNG

                     

                    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 Adhimulam

                        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
                          Deepak Kurup

                          hi ,

                           

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

                           

                           

                           

                          Deepak

                        • Re: Time Series and Parameters
                          Deepak Kurup

                          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

                            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

                              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
                                  Deepak Kurup

                                  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