Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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

11 Replies
Not applicable
Author

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

Not applicable
Author

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

Not applicable
Author

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

Not applicable
Author

//// First build the Totals

//

//PLFormat:

//LOAD

//LineNum,

//Type,

//Description,

//[GL Account Start],

//[GL Account End],

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

//FROM

//(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];

//

//

Not applicable
Author

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

Not applicable
Author

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

martin59
Specialist II
Specialist II

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

Not applicable
Author

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

Not applicable
Author

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;