Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
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

Tags (1)
11 Replies
Not applicable

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

Not applicable

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

Not applicable

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

Not applicable

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

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

Re: INTERVALMATCH

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

Not applicable

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

martin59
Valued Contributor II

Re: INTERVALMATCH

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

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

Not applicable

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;

Community Browser