topic Re: INTERVALMATCH in QlikView

INTERVALMATCH

Wed, 16 Jan 2013 19:08:06 GMT

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

Wed, 16 Jan 2013 21:05:14 GMT

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

Wed, 16 Jan 2013 21:09:39 GMT

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

Wed, 16 Jan 2013 21:16:41 GMT

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

Wed, 16 Jan 2013 21:20:17 GMT

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

Re: INTERVALMATCH

Wed, 16 Jan 2013 21:20:46 GMT

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

Re: INTERVALMATCH

Wed, 16 Jan 2013 21:32:11 GMT

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

martin59 — Wed, 16 Jan 2013 22:22:53 GMT

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

Thu, 17 Jan 2013 13:09:20 GMT

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

Thu, 17 Jan 2013 13:13:10 GMT

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;

Re: INTERVALMATCH

Thu, 17 Jan 2013 13:14:17 GMT

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

Thu, 17 Jan 2013 13:21:06 GMT

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;