topic Restricted Keyfigures ('If select' statement across multiple tables) in QlikView

Restricted Keyfigures ('If select' statement across multiple tables)

Fri, 05 Feb 2010 16:35:24 GMT

Hello QV Gurus,

I want to make an if statement across multiple tabels.

How can I solve this? This is a SAP BW cube, so it means Fact Table, Dimension Table and Attribute Table.

// if([Condition type_KSCHL]='YA01', [Cond Val (Exec rep)_/BIC/ZN_K_VEX]*[Exchange rate_EXCHG_RATE]) as 'Restricted Keyfigure_YA01'

Keyfigures:
LOAD
[Dimension Key_KEY_ZNSD_C161] as %ZNSD_C161_KEY,
[Cond Val (Exec rep)_/BIC/ZN_K_VEX],
[Exchange rate_EXCHG_RATE]

FROM
..FZNSD_C16.QVD (qvd);

//++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Dimension:

LOAD
[Dimension Key_DIMID] as %ZNSD_C161_KEY,
[SID_SID_0KSCHL] as %SKSCHL_KEY

FROM
..DZNSD_C161.qvd (qvd);

//++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Condition_Type:
LOAD
[SID_SID] as %SKSCHL_KEY,
[Condition type_KSCHL]

FROM
..SKSCHL.QVD (qvd);

//++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Restricted Keyfigures ('If select' statement across multiple tables)

disqr_rm — Fri, 05 Feb 2010 18:23:24 GMT

Somehow you need to join information. One option would be:

tMap1:
LOAD DISTINCT
%ZNSD_C161_KEY as a1,
%SKSCHL_KEY as a2
RESIDENT Dimension;

Left join (tMap1)
LOAD DISTINCT
%SKSCHL_KEY as a2,
[Condition type_KSCHL], as a3
RESIDENT Condition_Type;

Map:
MAPPING LOAD a1 as key, a3 as value RESIDENT tMap1:

drop table tMap1:

Keyfigures:
LOAD
[Dimension Key_KEY_ZNSD_C161] as %ZNSD_C161_KEY,
[Cond Val (Exec rep)_/BIC/ZN_K_VEX],
[Exchange rate_EXCHG_RATE],
if(applymap('Map', %ZNSD_C161_KEY, null()) ='YA01', [Cond Val (Exec rep)_/BIC/ZN_K_VEX]*[Exchange rate_EXCHG_RATE]) as 'Restricted Keyfigure_YA01'
FROM
..FZNSD_C16.QVD (qvd);

Not sure how performant it woudl be though.

I would rather make arrangements in the extractor itself so that I get [Condition type_KSCHL] joined to Dimension QVD itself.

Hope this helps.

Rakesh

Restricted Keyfigures ('If select' statement across multiple tables)

Mon, 08 Feb 2010 13:53:47 GMT

Hi Rakesh,

I had to make a few (small) changes to your script to let it work, like this.

LET v325='325';

tMap1:
LOAD DISTINCT
%ZNSD_C161_KEY as a1,
%SKSCHL_KEY as a2
RESIDENT Dimension;

Left join (tMap1)
LOAD DISTINCT
%SKSCHL_KEY as a2,
[Condition type_KSCHL] as a3
RESIDENT Condition_Type;

//++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Map:
MAPPING LOAD
a1 as key,
a3 as value
RESIDENT tMap1;
drop table tMap1;

//++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Keyfigures:
LOAD
[Dimension Key_KEY_ZNSD_C161] as %ZNSD_C161_KEY,
[Cond Val (Exec rep)_/BIC/ZN_K_VEX],
[Exch.rate-acct._EXRATE_ACC],
[Exchange rate_EXCHG_RATE],
// if(applymap( 'Map', [Dimension Key_KEY_ZNSD_C161], null() ),'325', [Cond Val (Exec rep)_/BIC/ZN_K_VEX]*[Exch.rate-acct._EXRATE_ACC]) as 'Restricted Keyfigure_Y013',
if(applymap( 'Map', [Dimension Key_KEY_ZNSD_C161], null() ),$(v325), [Cond Val (Exec rep)_/BIC/ZN_K_VEX]*[Exch.rate-acct._EXRATE_ACC]) as 'Restricted Keyfigure_Y013'

FROM
..FZNSD_C16.QVD
(qvd);

But it still doesn't do, what I want it to do. I still have to make a selection (Y013 or 325) first before it gives the correct result.

What I want is always this result, without selecting, because I have to make 40 different restricted Keyfigures, were the customer can select from, in his report.

There is a solution, with set-analysis, but I want to present these 40 restricted keyfigures as fields he can select from in his querying.

Sven

Restricted Keyfigures ('If select' statement across multiple tables)

disqr_rm — Mon, 08 Feb 2010 15:59:46 GMT

Hi Sven,

In that case, you would be better off merginf all tables in one, and then creating a final keyfigure table having your "final" keyfigures. Something like below will give you an idea. I have typed the code in notepad just to show an idea, not sure if it will work 100% with copy and paste. Let me know.


Dimension:
LOAD
 [Dimension Key_DIMID] as %ZNSD_C161_KEY,
 [SID_SID_0KSCHL] as %SKSCHL_KEY
FROM
..DZNSD_C161.qvd (qvd);
Left join (Dimension)
LOAD
 [SID_SID] as %SKSCHL_KEY,
 [Condition type_KSCHL]
FROM
..SKSCHL.QVD (qvd);
drop field %SKSCHL_KEY;
tKeyfigures:
LOAD
 [Dimension Key_KEY_ZNSD_C161] as %ZNSD_C161_KEY,
 [Cond Val (Exec rep)_/BIC/ZN_K_VEX],
 [Exchange rate_EXCHG_RATE]

FROM
..FZNSD_C16.QVD (qvd);
Left join (tKeyfigures)
LOAD DISTINCT
 %ZNSD_C161_KEY,
 [Condition type_KSCHL]
RESIDENT Dimension;
drop table Dimension;
KeyFigures:
LOAD DISTINCT
 [Dimension Key_KEY_ZNSD_C161] as %DimKey
FROM
..FZNSD_C16.QVD (qvd);
Left join (KeyFigures)
LOAD %ZNSD_C161_KEY as %DimKey,
 [Cond Val (Exec rep)_/BIC/ZN_K_VEX]*[Exchange rate_EXCHG_RATE] as [Restricted Keyfigure_YA01]
RESIDENT tKeyfigures
WHERE [Condition type_KSCHL] = 'YA01';
Left join (KeyFigures)
LOAD %ZNSD_C161_KEY as %DimKey,
 [Cond Val (Exec rep)_/BIC/ZN_K_VEX]*[Exchange rate_EXCHG_RATE] as [Restricted Keyfigure_YA03]
RESIDENT tKeyfigures
WHERE [Condition type_KSCHL] = 'YA03';
drop table tKeyfigures;

Restricted Keyfigures ('If select' statement across multiple tables)

Wed, 24 Feb 2010 09:32:05 GMT

Hi Rakesh,

your solution as described works, but just for a limited qty of calculated / restricted KF's.

Above 20 KF's we suffered performance issues on the server.

But I found another solution which works fine.

I put the complete KF calculation (SET ANALYSIS) in a SET statement as a variable.

The user just has to select the wanted variable and put -> =$(variable ) in the Chart.

Many thanks for your suggestion