INTERVALMATCH

[#1]

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

[#2]

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

[#3]

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

[#4]

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

[#5]

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

//

//

[#6]

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

[#7]

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]

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

[#9]

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

[#10]

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;