Inline data mapping?

[#1]

Hi,

I have question about data mapping for dashboard script.

Consider QVD is providing you data in below format

Name	Cost per Lb ($)	Qty in Warehouse
Mango	10	500
Apple	15	1000
Oranges	10	50

But in this qvd one of the field 'country of origin' is missing. Now as it is not in QVD, I need to create add my own mapping as below:-

Mango-Mexico

Apple-New Zealand

Oranges - Costa Rica

How to add this extra mapping in Qlikview data load script?

[MayilVahanan]

Hi,

     Try this,

     In Script,

          Load * from filename.qvd(qvd);

     Then

     Load * inline

     [

     Name,[Country of Origin]

     Mango,Mexico

     Apple,New Zealand

     Oranges,Costa Rica

     ];

     Then reload it.It mapes name,cost,qty and country.

     Hope it helps.

[#3]

Hi Andrepeter,

You can use applymap to add reference. Do a mapping load of the Name,country and apply the map in your data load script for creating QVD.

Refer ApplyMap in QV help for syntax. Hope this helps you.

Regards,

Kiran Rokkam.

[#4]

Hi Mayil,

Your anwser was helpful, but i have one more question.

What if [Country of Origin] is present in another qvd.

For e.g Fruit Table is as mentioned in original question. This QVd doesn't have country of origin.

Now we have one more table for crops with [Country of Origin] present.

Crop_Type,Cost per Lb ($),Qty,[Country of Origin]

Wheat,10,1000,Argentina

Corn,10,2000, USA

Now I have two different QVDs, one giving Fruit data from fruits.qvd and another giving crops data from crops.qvd.

And I need to analyze both with respect of [Country of Origin].

Can I use [Country of Origin] as a variable name in inline method?

P.S:- QVDs will be different for fruits as well as crop.

[MayilVahanan · Accepted solution]

Hi

     By use concatenate method, you can combine the fruits.qvd file with inline table where map the country to fruits. Then another qvd file maps based on [country of origin] in it..Make sure, both table contain the [Country of origin] field after reload it using table view.

Hope it helps.

[#6]

Re: Inline data mapping?  

Hi Mayil,

Your anwser was helpful, but i have one more question.

What if [Country of Origin] is present in another qvd.

For e.g Fruit Table is as mentioned in original question. This QVd doesn't have country of origin.

Now we have one more table for crops with [Country of Origin] present.

Crop_Type,Cost per Lb ($),Qty,[Country of Origin]

Wheat,10,1000,Argentina

Corn,10,2000, USA

Now I have two different QVDs, one giving Fruit data from fruits.qvd and another giving crops data from crops.qvd.

And I need to analyze both with respect of [Country of Origin].

Can I use [Country of Origin] as a variable name in inline method?

P.S:- QVDs will be different for fruits as well as crop

[#7]

Yes you can.

Use the concatenate function

(logic)

FRUITS:

Load * from fruits.qvd;

join

inline ();

concatenate (FRUITS)

LOAD * from crops.qvd;

result:

Name	Cost per Lb ($)	Qty in Warehouse,[Country of Origin], Crop_Type,Cost per Lb ($),Qty

if you create a table control

Country origin, sum(qty in Warehouse), sum (qty) you will be able to compare both.

[#8]

Thanks Mayil and Adrian. Your answers really helped me.