Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

rams_school
New Contributor III

Replacing ProductID with ProductName in Report

I have the following Table(Sample Extracted)

TableTransaction

     

yearCountryCodeProductIDValueTotalValue
2008ago1025750820562
2008ago10509094543
2008ago10308501
2008ago1073770.08786546
2008ago1050253400
2008ago1030141861
2010btn105011850.6
2010btn10403953.46
2010btn10313962390
2010btn10601604.74
2010btn108020053.13
2014ven107017304
2014ven107167521202
2014ven1010487042
2014ven10126486179841
2014ven1050301279

TableCountry

  

CountryCodeCountryName
agoAngola
btnBhutan
venVenezuela

TableProduct

  

ProductIDProductName
101Cerels
102Almonds
103Cashew nuts
104Corn
105Palm Oil
106Olive Oil
107Dried Fruits
108Processed Vegetables(Mangoes)

Now while creating the chart, using TableTransaction, I would like to display 'CountryName' instead of CountryCode, Display ProductName instead of ProductID

how do I do it?

I am using QlikView instead of QlikSense as I am unable to install QlikSense on my computer.

Thank you

Tags (1)
4 Replies
phaneendra_kunc
Valued Contributor III

Re: Replacing ProductID with ProductName in Report

You have correct table structure. Qlikview will automatically link the fileds that has same names. in this case "CountryCode" from TableTransaction and TableCountry will automatically link and same with your ProductID.

If you hit CTRL+T you will see the linkage.

So when you create a chart (Straight table) just add the necessary dimensions and a metric you are interested in.

rams_school
New Contributor III

Re: Replacing ProductID with ProductName in Report

Thanks Phaneendra Kunche

Qlikview automatically links the Table when I hit CTRL+T. The Links as follows

http://i.imgur.com/BTK4dIj.png

Here is my code when I select CTRL +E

http://i.imgur.com/SAcd7dm.png

My Screen chart looks like below

http://i.imgur.com/Kc2aEt6.png

Given my structure of Table, I would like to replace the 3 digit country code with Actual country name. Example on the chart instead of 'ago' I should get 'Angola'

My Data size is very long. One of the table is nearly 500MB Text file. I have extracted small portion to show here. Thanks

How do I implement?

Thanks

oscar_ortiz
Valued Contributor

Re: Replacing ProductID with ProductName in Report

Are you simply wanting to change the charts column from CountryCode to CountryName?

Or did you want to modify your data model structure?

Using a Mapping Load and ApplyMap sounds like your option.

Load your COUNTRY and PRODUCTS tables as Mapping Load then ApplyMap in your script:

MapProduct:

Mapping

Load

  ProductID,

  ProductName

From YourTable;

Transactions:

Load

  Fields,

  ApplyMap('MapProduct', ProductID) as ProductName

From YourOtherTable;

nizamsha
Valued Contributor

Re: Replacing ProductID with ProductName in Report

Load all the table it will link automatically

Table product is linked to transaction using ProductID

Table Country is linked to transaction using CountryCode

Or

incase u dont want to keep the Product and CountryCode table in your DataModel use ApplyMap


Product:

Mapping ProductID,ProductName From ProductTable;


Country:

Mapping CountryCode,CountryName From CountryTable;


Transaction:

Load 

     ApplyMap('Product',ProductID) as ProductName,

ApplyMap('Country',CountryCode) as CountryName,

       Year,

       CountryCode,

       ProductID,

      Value,

     TotalValue From Transaction;


Or U can Use join it will also give the same result single transaction Table


Transaction:

Load 

     Year,

       CountryCode,

       ProductID,

      Value,

     TotalValue From Transaction;

Join(Transaction)

Product:

ProductID,ProductName From ProductTable;

Join(Transaction)

Country:

CountryCode,CountryName From CountryTable;


choose according to you what u want

even u can go for left join because its a master(Product,Country) but in case if u want these master from these master then u have to again extract from the transaction using applymap