Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Not able to understand apply map :new to qlikview

Hi i have 6 tables need to use applu map and join these into one table

Please find the table script structure :

LOAD SUB_COMMODITY_CODE,
     SUB_COMMODITY_CODE_DESC
FROM
[\\irvqlkapp06\q$\QlikViewDocuments\New Framework\Data\QMart\Dimensions\SUB_COMMODITY .qvd]
(qvd);

LOAD ID,
     GLOBAL_SUPPLIER_ID,
     CAPABILITY_ID
FROM
[\\irvqlkapp06\q$\QlikViewDocuments\New Framework\Data\QMart\Dimensions\SUPPLIER_CAPABILITIES .qvd]
(qvd);

LOAD COMMODITY_CODE,
     CAPABILITY_ID,
     CAPABILITY
FROM
[\\irvqlkapp06\q$\QlikViewDocuments\New Framework\Data\QMart\Dimensions\CAPABILITIES .qvd]
(qvd);

LOAD COMMODITY_CODE,
     COMMODITY_CODE_DESC
FROM
[\\irvqlkapp06\q$\QlikViewDocuments\New Framework\Data\QMart\Dimensions\COMMODITY .qvd]
(qvd);

LOAD SUB_COMMODITY_CODE,
     COMMODITY_MANAGER
FROM
[\\irvqlkapp06\q$\QlikViewDocuments\New Framework\Data\QMart\Dimensions\COMMODITY_MANAGER .qvd]
(qvd);

LOAD ID,
     GLOBAL_SUPPLIER_ID,
     SUB_COMMODITY_CODE
FROM
[\\irvqlkapp06\q$\QlikViewDocuments\New Framework\Data\QMart\Dimensions\PREFRD_SUPPLIER_COMMODITY .qvd]
(qvd);

Please gudie me on this

 

Tags (1)
12 Replies

Re: Not able to understand apply map :new to qlikview

Reddy

This Blog Post Don't join - use Applymap instead by Henric Cronström is good.

Best Regards,     Bill

Not applicable

Re: Not able to understand apply map :new to qlikview

 

COMMODITY:

Mapping LOAD
COMMODITY_CODE,
COMMODITY_CODE_DESC

FROM
[\\irvqlkapp06\QlikViewDocuments2\New Framework\Data\QMart\Dimensions\COMMODITY .qvd]
(
qvd);

COMMODITY_MANAGER:
Mapping LOAD SUB_COMMODITY_CODE,
COMMODITY_MANAGER
FROM
[\\irvqlkapp06\QlikViewDocuments2\New Framework\Data\QMart\Dimensions\COMMODITY_MANAGER .qvd]
(
qvd);

//LOAD ID,
// GLOBAL_SUPPLIER_ID,
// SUB_COMMODITY_CODE
//FROM
//[\\irvqlkapp06\QlikViewDocuments2\New Framework\Data\QMart\Dimensions\PREFRD_SUPPLIER_COMMODITY .qvd]
//(qvd);
SUPPLIER_CAPABILITIES:
LOAD ID,
GLOBAL_SUPPLIER_ID as [GLOBAL_SUPPLIER_CODE],
CAPABILITY_ID,
ApplyMap('COMMODITY_MANAGER',CAPABILITY_ID,Null()) As COMMODITY_MANAGER

FROM
[\\irvqlkapp06\QlikViewDocuments2\New Framework\Data\QMart\Dimensions\SUPPLIER_CAPABILITIES .qvd]
(
qvd);
Join
LOAD COMMODITY_CODE,
CAPABILITY_ID,
CAPABILITY
FROM
[\\irvqlkapp06\QlikViewDocuments2\New Framework\Data\QMart\Dimensions\CAPABILITIES .qvd]
(
qvd);

LOAD * Resident SUPPLIER_CAPABILITIES;
Join
LOAD SUB_COMMODITY_CODE,
SUB_COMMODITY_CODE_DESC,
ApplyMap('COMMODITY', COMMODITY_CODE,Null()) As COMMODITY_CODE_DESC

FROM
[\\irvqlkapp06\QlikViewDocuments2\New Framework\Data\QMart\Dimensions\SUB_COMMODITY .qvd]
(
qvd);

where i am going wrong ..??

Re: Re: Not able to understand apply map :new to qlikview

Reddy

I have just taken a couple of bits

COMMODITY:

Mapping LOAD

COMMODITY_CODE,

COMMODITY_CODE_DESC

FROM

[\\irvqlkapp06\QlikViewDocuments2\New Framework\Data\QMart\Dimensions\COMMODITY .qvd]

(qvd);

ResultTable:

LOAD

COMMODITY_CODE,

applymap ( 'COMMODITY' , COMMODITY_CODE ) as COMMODITY_CODE_DESC ,

CAPABILITY_ID,

CAPABILITY

FROM

[\\irvqlkapp06\QlikViewDocuments2\New Framework\Data\QMart\Dimensions\CAPABILITIES .qvd]

(qvd);

And added an Applymap into it.

Assuming it works (I cannot test without your qvd's) does this example help you ?

Best Regards,     Bill

Not applicable

Re: Re: Not able to understand apply map :new to qlikview

yeah i got that .

Can i get the same for commodity manager table and preferrese supplier capabilites ,subcomodiyt tables

Re: Re: Re: Not able to understand apply map :new to qlikview

Reddy

Yup, you should be able to [I have not studied your data model though].

If you are not on the road yet, could you distill out a specific bit you need to ask a question on.

Basically for Applymap, you create your mapping table with :

  • Key
  • Value

And for your ApplyMap you provide

  • Map Name
  • Key

and it returns the Value.

Best Regards,     Bill

alexis
Contributor III

Re: Not able to understand apply map :new to qlikview

Hi Reddy,

There are a few comments that I can make looking at your code. Some are just good coding practises and some are perhaps reasons why your code is not working.

Starting from the top, let's take each table in turn:

a) COMMODITY table - no problems

b) COMMODITY_MANAGER: Here you have named one of the fields (COMMODITY_MANAGER) the same as the table - this is not a good programming practice and best not used to avoid confusion. One way that I usually get around naming my tables is to use the "plural" for the table name so in this case "COMMODITY_MANAGERS"

c) SUPPLIER_CAPABILITIES:
In this instance your ApplyMap statement is calling "COMMODITY_MANAGER" (the table) passing the CAPABILITY_ID and getting back the "COMMODITY_MANAGER" from the table "COMMODITY_MANAGER" and you decide to name the result .... COMMODITY_MANAGER!!!  Apart from being totally confusing (because you have the same name for the field and table you are calling) but naming the result COMMODITY_MANAGER again, does add to the confusion but more importantly, establishes a link between the field "COMMODITY_MANAGER" in SUPPLIER_CAPABILITIES and "COMMODITY_MANAGER" in COMMODITY_MANAGER" - was that what you intended to do?

Further down you JOIN with the set of data that is coming from CAPABILITIES.QVD - this seems fine as you are joining on one common field, the CAPABILITY_ID

Beyond this point I lose you as you appear to be loading the very table that you are constructing (SUPPLIER_CAPABILITIES)  without creating another table just before the LOAD statement below, which is what I presume you wanted to do. In other words, whilst you are still in the scope of SUPPLIER_CAPABILITIES.

Even if that worked you are then attempting to JOIN with the contents of the SUB_COMMODITY QVD file where you do not have ANY fields in common. None of SUB_COMMODITY_CODE, SUB_COMMODITY_CODE or COMMODITY_CODE DESC are present in the SUPPLIER_CAPABILITIES so there would be no join happening there.


LOAD * Resident SUPPLIER_CAPABILITIES;
Join
LOAD SUB_COMMODITY_CODE,
SUB_COMMODITY_CODE_DESC,
ApplyMap('COMMODITY', COMMODITY_CODE,Null()) As COMMODITY_CODE_DESC

FROM ......

Hope this helps in getting your code to work.

Alexis

Not applicable

Re: Not able to understand apply map :new to qlikview

That was helpful ..stil couldnt figure it out

please find my requirment:


These are the 6 tables i have and thier structure  i need to join to fact table with Global_supplier_id

I need these fields

SUB_COMMODITY_CODE,

     SUB_COMMODITY_CODE_DESC

CAPABILITY_ID

COMMODITY_CODE

CAPABILITY

COMMODITY_CODE_DESC

COMMODITY_MANAGER

GLOBAL_SUPPLIER_ID TO JOIN WITH MY FACT TABLE

Please find the each table structure.

LOAD SUB_COMMODITY_CODE,
     SUB_COMMODITY_CODE_DESC
FROM
[\\irvqlkapp06\q$\QlikViewDocuments\New Framework\Data\QMart\Dimensions\SUB_COMMODITY .qvd]
(qvd);

LOAD ID,
     GLOBAL_SUPPLIER_ID,
     CAPABILITY_ID
FROM
[\\irvqlkapp06\q$\QlikViewDocuments\New Framework\Data\QMart\Dimensions\SUPPLIER_CAPABILITIES .qvd]
(qvd);

LOAD COMMODITY_CODE,
     CAPABILITY_ID,
     CAPABILITY
FROM
[\\irvqlkapp06\q$\QlikViewDocuments\New Framework\Data\QMart\Dimensions\CAPABILITIES .qvd]
(qvd);

LOAD COMMODITY_CODE,
     COMMODITY_CODE_DESC
FROM
[\\irvqlkapp06\q$\QlikViewDocuments\New Framework\Data\QMart\Dimensions\COMMODITY .qvd]
(qvd);

LOAD SUB_COMMODITY_CODE,
     COMMODITY_MANAGER
FROM
[\\irvqlkapp06\q$\QlikViewDocuments\New Framework\Data\QMart\Dimensions\COMMODITY_MANAGER .qvd]
(qvd);

LOAD ID,
     GLOBAL_SUPPLIER_ID,
     SUB_COMMODITY_CODE
FROM
[\\irvqlkapp06\q$\QlikViewDocuments\New Framework\Data\QMart\Dimensions\PREFRD_SUPPLIER_COMMODITY .qvd]
(qvd);

alexis
Contributor III

Re: Not able to understand apply map :new to qlikview

Taking the QVDs that you are using and a lot of guess-work I have nearly solved your issues/requirements but certain questions still remain.

SUB_COMMODITY_MAP:

MAPPING LOAD SUB_COMMODITY_CODE,

  SUB_COMMODITY_CODE_DESC

FROM

[\\irvqlkapp06\QlikViewDocuments2\New Framework\Data\QMart\Dimensions\SUB_COMMODITY .qvd]

(qvd);

COMMODITY_MANAGER_MAP:

Mapping LOAD SUB_COMMODITY_CODE,

  COMMODITY_MANAGER

FROM

[\\irvqlkapp06\QlikViewDocuments2\New Framework\Data\QMart\Dimensions\COMMODITY_MANAGER .qvd]

(qvd);

COMMODITY:

Mapping LOAD COMMODITY_CODE,

  COMMODITY_CODE_DESC

FROM

[\\irvqlkapp06\QlikViewDocuments2\New Framework\Data\QMart\Dimensions\COMMODITY .qvd]

(qvd);

CAPABILITIES:

LOAD COMMODITY_CODE,

  CAPABILITY_ID,

  CAPABILITY

FROM

[\\irvqlkapp06\QlikViewDocuments2\New Framework\Data\QMart\Dimensions\CAPABILITIES .qvd]

(qvd);

SUPPLIER_CAPABILITIES_MAP:

MAPPING LOAD

  //ID,

  //GLOBAL_SUPPLIER_ID as [GLOBAL_SUPPLIER_CODE],

  ID & '/' & GLOBAL_SUPPLIER_ID As SupplierCap_To_PrefSupplier_KEY

  CAPABILITY_ID,

FROM

[\\irvqlkapp06\QlikViewDocuments2\New Framework\Data\QMart\Dimensions\SUPPLIER_CAPABILITIES .qvd]

(qvd);

PREF_SUPPLIER:

LOAD *,

  // Here I would use the CapabilityID (see below) to connect with CAPABILITIES and COMMODITY above

  // but cannot see how I can "connect" as we don't appear to have uniqueness to extract either the

  // capability or commodity.. so that is the answer that you need to provide - what is the relationship

  // between suppliers, commodities and capabilities

;

LOAD ID,

      GLOBAL_SUPPLIER_ID,

  // I am assuming here that the combination of ID and GLOBAL_SUPPLIER_ID will give us a unique CAPABILITY_ID

  ApplyMap('SUPPLIER_CAPABILITIES_MAP', ID & '/' & GLOBAL_SUPPLIER_ID, Null()) As CapabilityID,

      SUB_COMMODITY_CODE,

  ApplyMap('SUB_COMMODITY_MAP',SUB_COMMODITY_CODE, Null()) As SubCommodityDescription,

  ApplyMap('COMMODITY_MANAGER_MAP',SUB_COMMODITY_CODE, Null()) As CommodityManager

FROM

[\\irvqlkapp06\q$\QlikViewDocuments\New Framework\Data\QMart\Dimensions\PREFRD_SUPPLIER_COMMODITY .qvd]

(qvd);

Re: Not able to understand apply map :new to qlikview

Reddy

I usually find it helps if I draw a diagram of the Tables & Fields showing which Key Link points to what Value.

Then I steadily build up the script one ApplyMap() at a time.  It sounds like your starting point should be GLOBAL_SUPPLIER_ID so I'd suggest you build from there.

If you come across any specific ApplyMap() causing you a problem then feel free to ask.

Best Regards,     Bill

Community Browser