Qlik Community

QlikView Connectors

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Inserting a date variable in PseudoMDX D - SAP OLAP Connector

Hi

My query looks something like this:

[Query]:
Load *;
Select PseudoMDX D (
Dimensions (
  (),
  (),
  (),
  (),
  [CALMONTH] ()),
Measures (
  [1],
  [2],
  [3],
  [4],

Variables (
  [0TYEAR] (I [CALMONTH].[201401]:[CALMONTH].[201403])),
From (Query));

Since there are limitations with the amount of data I can retrieve, I have to retrieve data every 3 months at a time.

There is also no Primary Key - hence I cannot do Incremental Loads.

Is there a way to loop through the query but then changing the date range (To the next three months)? - Maybe using a variable or any other way for that matter?

Any help would be appreciated - Thanks!

Magen

3 Replies
yfe
Not applicable

Re: Inserting a date variable in PseudoMDX D - SAP OLAP Connector

Hi

You can do this:

for vMonth=201401 to 201412

     DATA:

     Select PseudoMDX D (
     Dimensions (
       (),
       (),
       (),
       (),
       [CALMONTH] ()),
     Measures (
       [1],
       [2],
  ...

     Variables (
       [0TYEAR] (I [CALMONTH].[$(vMonth)]:[CALMONTH].[$(vMonth)])),
     From (Query));

next vMonth

This loop will return the query results for each calendar month. The data will be aggregated to table DATA.

Not applicable

Re: Inserting a date variable in PseudoMDX D - SAP OLAP Connector

Hi Yaniv

Thanks that works great, but, what if i want to retrieve data over 2 years.

So for example 201301 to 201412.

I cannot declare this in the variable as it doesn't recognize it to be a year and month.

Hence if i attempt to run it, it would return 201301, 201302,...201312, 201313, 201314 and so on ...

Any idea?

Regards,

Magen

yfe
Not applicable

Re: Inserting a date variable in PseudoMDX D - SAP OLAP Connector

Hi Magen

Been busy during the last couple of days...

You should use nested loop, for example:

for year = year(today()) to year(today())+1

  for period = 0 to 12

  if len(period) = 1 then

  let v_period = '0'.

  else

  let v_period = period;

  endif

  Select PseudoMDX D (

      Dimensions (

        (),

        (),

        (),

        (),

        [CALMONTH] ()),

      Measures (

        [1],

        [2],

        [3],

        [4],

      Variables (

  [0VYEAR] (I = [0CALYEAR].[$(year)]),

  [0VCALMONTH] (I = [0CALMONTH].[$(v_period)])),

      From (Query));

  next period

next year

Thanks

Yaniv