11 Replies Latest reply: Jan 17, 2013 8:21 AM by Zhenya Virina RSS

    INTERVALMATCH

      Hi -

       

      Can someone please help me with figuring out INTERVALMATCH issue that i am having when running QV script ( i am new to QV)

       

       

      Step 1. I am using MDX statement to get Account infromaiton:

       

                     Temp Table:

                               SELECT

                                 { null } on 0,

                                       {DESCENDANTS([Account])}on 1   

                                         FROM [Cube];


      Step 2. AND then i need to run intervalmatch formula that looks like this

       

                     IntervalMatchTemp:

                     INTERVALMATCH [Account] LOAD [GL Account Start], [GL Account End] RESIDENT Layout;

       

      SCRIPT ERROR

       

      Field is not Found

      IntervalMatchTemp:

      INTERVALMATCH {[([Account])} LOAD [GL Account Start], [GL Account End] RESIDENT Layout

       

      Please help!

       

      Thank you

        • Re: INTERVALMATCH

          Hello,

           

          Could you give us a sample of your application ?

           

          In a first time, I think your syntax is not correct.

           

          You can try this :

          IntervalMatchTemp:

          INTERVALMATCH (Account) LOAD [GL Account Start], [GL Account End] RESIDENT Layout;

           

          Amand Dupretz

            • Re: INTERVALMATCH

              Hi Amand,

              Thank you for looking into this.

               

              Unfortunately, I don't know how to provide you with a sample of data without exposing too much information on-line. I tried using different brackets ({[) and combinations but could not get the query to work.

               

              Do you have any other suggestions?

               

              Essentially I am trying do the same as someone posted here.

               

              http://community.qlik.com/thread/40936

                • Re: INTERVALMATCH

                  I just need the script of your application, not the data in order to understand what you wanna do.

                   

                  You can clear it before attach it.

                   

                   

                  Amand Dupretz

                    • Re: INTERVALMATCH

                      //// First build the Totals

                      //

                      //PLFormat:

                      //LOAD

                      //LineNum,

                      //Type,

                      //Description,

                      //[GL Account Start],

                      //[GL Account End],

                      //if(Sign = -1,-1,1) as Sign

                      //FROM [S:\Finance\Public\Financial Systems\BPC\PAndLInput.xlsx]

                      //(ooxml, embedded labels, table is PAndL)

                      //WHERE Type <> 'Total'

                      //;

                      //

                      //Totals:

                      //LOAD DISTINCT

                      //LineNum as HeaderLineNum,

                      //Description as Header,

                      //[Total Start],

                      //[Total End],

                      //if(Sign = -1,-1,1) as Sign

                      //FROM PAndLInput.xlsx

                      //(ooxml, embedded labels, table is PAndL)

                      //WHERE Type = 'Total';

                      //

                      //INNER JOIN INTERVALMATCH (LineNum) LOAD [Total Start], [Total End] RESIDENT Totals;

                      //

                      //

                      //LEFT JOIN (Totals)

                      //LOAD

                      //LineNum,

                      //[GL Account Start],

                      //[GL Account End]

                      //RESIDENT PLFormat;

                      //

                      //

                      //DROP TABLE PLFormat;

                      //

                      //

                      //// Now load the Layout and then Add the Totals

                      //

                      //Layout:

                      //LOAD

                      //LineNum,

                      //Type,

                      //Description,

                      //[GL Account Start],

                      //[GL Account End],

                      //if(len(trim([GL Account Start])) = 0,'*',[GL Account Start] & '|' & [GL Account End]) as GLStartEnd,

                      //if(Sign = -1,-1,1) as Sign

                      //FROM PAndLInput.xlsx

                      //(ooxml, embedded labels, table is PAndL)

                      //WHERE Type <> 'Total'

                      //;

                      //

                      //

                      //

                      //CONCATENATE (Layout)

                      //LOAD

                      //HeaderLineNum AS LineNum,

                      //'Total' AS Type,

                      //Header AS Description,

                      //[GL Account Start],

                      //[GL Account End],

                      //if(len(trim([GL Account Start])) = 0,'*',[GL Account Start] & '|' & [GL Account End]) as GLStartEnd,

                      //Sign

                      //resident Totals;

                      //

                      //drop table Totals;

                      //

                      //PLFormat:

                      //LOAD * Inline [

                      //Type,Format

                      //LineItem,

                      //Total,"'<B>''<I>'"

                      //Space,

                      //];

                       

                      //PLTemp:

                      //SELECT

                      //  { null } on 0,

                      //        ([Account].[H1].[LEV14])on 1

                      //FROM [Cube];

                      //

                      //IntervalMatchTemp:

                      //INTERVALMATCH ("[Account].[H1].[LEV14]") LOAD [GL Account Start], [GL Account End] RESIDENT Layout;

                      //

                      //IntervalMatch:

                      //Load

                      //distinct

                      //("[Account].[H1].[LEV14]"),

                      //[GL Account Start] & '|' & [GL Account End] as GLStartEnd

                      //resident

                      //IntervalMatchTemp;

                      //

                      //drop table IntervalMatchTemp;

                      //

                      //

                      //Drop table PLTemp;

                      //

                      //Drop fields [GL Account Start], [GL Account End] from IntervalMatch;

                      //

                      //SELECT [Measures].[SIGNEDDATA] ON 0

                      //,

                      //NON EMPTY

                      //    (

                      //        [Time].[[FY2013.TOTAL].children,

                      //        HIERARCHIZE(DESCENDANTS([Account].[H1].[LEV14],

                      //        [Account].[H1].[LEV12],

                      //        SELF_BEFORE_AFTER)))

                      //               ON 1

                      //FROM [Cube];

                      //

                      //

                        • Re: INTERVALMATCH

                          I don't know the MDX language, and if it is supported by QlikView, but you can try this :

                           

                          PLTemp:
                          Load *;
                          SQL SELECT
                          { null } on 0,
                          ([Account].[H1].[LEV14])on 1
                          FROM [Cube];

                           

                           

                          If it doesn't work, you can go here :  http://xthview.com/index.php/download/

                           

                           

                          Amand Dupretz

                            • Re: INTERVALMATCH

                              Hi Amand,


                              The problem is not with MDX, this statement works with no problems. The problem is with intervalmatch query. It does not recognize [Account].[H1].[LEV14] as an available field.

                               

                              //PLTemp:

                              //SELECT

                              //  { null } on 0,

                              //        ([Account].[H1].[LEV14])on 1

                              //FROM [Cube];

                              //

                              //IntervalMatchTemp:

                              //INTERVALMATCH ("[Account].[H1].[LEV14]") LOAD [GL Account Start], [GL Account End] RESIDENT Layout;

                               

                              Thank you

                                • Re: INTERVALMATCH

                                  Hi,

                                   

                                  Could you write " exit script " after the loading of PLTemp and then, take a screenshot of the Table Viewer in order to see what is the name of your field after the load ?

                                   

                                  Amand Dupretz

                                    • Re: INTERVALMATCH

                                      Start and End date are part of a different query. Which is work

                                       

                                      PLTemp:

                                      SELECT

                                        { null } on 0,

                                              ([Account].[H1].[LEV14])on 1

                                      FROM [Cube];

                                       

                                       

                                      IntervalMatchTemp:

                                      INTERVALMATCH ("[Account].[H1].[LEV14]") LOAD [GL Account Start], [GL Account End] RESIDENT Layout;

                                       

                                      IntervalMatch:

                                      Load

                                      distinct

                                      ("[Account].[H1].[LEV14]"),

                                      [GL Account Start] & '|' & [GL Account End] as GLStartEnd

                                      resident

                                      IntervalMatchTemp;

                                       

                                      drop table IntervalMatchTemp;

                                       

                                       

                                      Drop table PLTemp;

                                       

                                      Drop fields [GL Account Start], [GL Account End] from IntervalMatch;

                              • Re: INTERVALMATCH

                                The section that is failing for me is highlighted in Bold.

                                  • Re: INTERVALMATCH
                                    Martin FAVIER

                                    Hi,

                                    You can try this :

                                     

                                    PLTemp:

                                    SQL SELECT

                                    { null } on 0,

                                    ([Account].[H1].[LEV14]) on 1

                                    FROM [Cube];

                                     

                                    Pay attention to have a space character before the key word "on"

                                     

                                    Hope that helps you

                                     

                                    Martin Favier

                                      • Re: INTERVALMATCH

                                        Hi Martin,

                                         

                                        My MDX query works, i can see data on my report. I am having trouble with using intermatch statement. QV does not recognize Account field as an available option.

                                         

                                         

                                         

                                        [Account].[H1].[LEV14] as an available field.

                                         

                                        //PLTemp:

                                        //SELECT

                                        //  { null } on 0,

                                        //        ([Account].[H1].[LEV14])on 1

                                        //FROM [Cube];

                                        //

                                        //IntervalMatchTemp:

                                        //INTERVALMATCH ("[Account].[H1].[LEV14]") LOAD [GL Account Start], [GL Account End] RESIDENT Layout;