Book - Qlikview 11 for Developers - Using MAPPING tables

[#1]

In page 234, it says Select the value Unknown from the  Aircraft Manufacturer Country list-box. I don't see unknown listed. I only see Canada, France, Italy and USA. I don't know why I am not able to see unknown.

My script in the Airports tab -

Map_Manufacturer_Country:

MAPPING LOAD Company,

     Country

FROM

[..\Data Files\CSVs\Aircraft_Manufacturers.csv]

(txt, codepage is 1252, embedded labels, delimiter is ';', msq);

[Aircraft Types]:

LOAD AC_TYPEID as [%Aircraft Type ID],

     AC_GROUP as [%Aircraft Group Type],

     SSD_NAME as [Aircraft Name],

     MANUFACTURER as [Aircraft Manufacturer],

     LONG_NAME as [Aircraft Name Full],

     SHORT_NAME as [Aircraft Name Abbreviated],

     BEGIN_DATE as [Aircraft Begin Date],

     END_DATE as [Aircraft End Date]

FROM

[..\Data Files\CSVs\Aircraft_Base_File.csv]

(txt, codepage is 1252, embedded labels, delimiter is ';', msq);

LEFT JOIN ([Aircraft Types])

LOAD [Aircraft Group ID] as [%Aircraft Group Type],

     [Aircraft Engine Type],

     [Aircraft Number Of Engines]

FROM

[..\Data Files\CSVs\Aircraft_Group.csv]

(txt, codepage is 1252, embedded labels, delimiter is ';', msq);

DROP FIELD [%Aircraft Group Type]

FROM [Aircraft Types];

CONCATENATE([Aircraft Types])

LOAD AC_TYPEID as [%Aircraft Type ID],

     SubField(AC_GROUPNAME, ',', 1) as [Aircraft Engine Type],

     SubField(AC_GROUPNAME, ', ', 2) as [Aircraft Number Of Engines],

     SSD_NAME as [Aircraft Name],

     MANUFACTURER as [Aircraft Manufacturer],

  ApplyMap('Map_Manufacturer_Country', MANUFACTURER, 'Unknown') as [Aircraft Manufacturer Country],

     LONG_NAME as [Aircraft Name Full],

     SHORT_NAME as [Aircraft Name Abbreviated],

     BEGIN_DATE as [Aircraft Begin Date],

     END_DATE as [Aircraft End Date]

FROM

[..\Data Files\CSVs\Aircraft_2010_Update.csv]

(txt, codepage is 1252, embedded labels, delimiter is ';', msq);

[ngulliver]

Hi, John.

ApoIogies if you know this already.

I haven't read this training but what it seems to be suggesting is that there should be an exception in your ApplyMap.

What I mean by that is that it is reading in your Aircraft_Manufacturers.csv  . Further down in the code you are comparing the MANUFACTURER with your list of manufacturers in Aircraft_Manufacturers.csv. If there is no match 'Unknown' is written back.

Since you are not getting an 'Unknown', it could well be that all manufacturers in the data are also present in the .csv - can you check your raw data ?

Regards,

Neil

[#3]

Yes, there is no null/unknown in the source csv file. May be its an error in the book.

[jaimeaguilar]

Hi,

it seems that there's a misunderstanding. there's no null/unknown value in your csv source file. Instead, the "unknown" value will be inserted by the third parameter of Applymap() function, in case that there no coincidence between the mapping table and the result table. In fact, this is one of the advantages of using Applymap() over joins, because you can handle scenarios when there are no coincidences and so avoid nulls,

regards

[Anonymous]

You need to put ApplyMap on the first Aircraft Types Table as well..partner

Map_Manufacturer_Country:

MAPPING LOAD Company,

     Country

FROM

[..\Data Files\CSVs\Aircraft_Manufacturers.csv]

(txt, codepage is 1252, embedded labels, delimiter is ';', msq);

[Aircraft Types]:

LOAD AC_TYPEID as [%Aircraft Type ID],

     AC_GROUP as [%Aircraft Group Type],

     SSD_NAME as [Aircraft Name],

     MANUFACTURER as [Aircraft Manufacturer],

    ApplyMap('Map_Manufacturer_Country', MANUFACTURER, 'Unknown') as [Aircraft Manufacturer Country],

     LONG_NAME as [Aircraft Name Full],

     SHORT_NAME as [Aircraft Name Abbreviated],

     BEGIN_DATE as [Aircraft Begin Date],

     END_DATE as [Aircraft End Date]

FROM

[..\Data Files\CSVs\Aircraft_Base_File.csv]

(txt, codepage is 1252, embedded labels, delimiter is ';', msq);

LEFT JOIN ([Aircraft Types])

LOAD [Aircraft Group ID] as [%Aircraft Group Type],

     [Aircraft Engine Type],

     [Aircraft Number Of Engines]

FROM

[..\Data Files\CSVs\Aircraft_Group.csv]

(txt, codepage is 1252, embedded labels, delimiter is ';', msq);

DROP FIELD [%Aircraft Group Type]

FROM [Aircraft Types];

CONCATENATE([Aircraft Types])

LOAD AC_TYPEID as [%Aircraft Type ID],

     SubField(AC_GROUPNAME, ',', 1) as [Aircraft Engine Type],

     SubField(AC_GROUPNAME, ', ', 2) as [Aircraft Number Of Engines],

     SSD_NAME as [Aircraft Name],

     MANUFACTURER as [Aircraft Manufacturer],

  ApplyMap('Map_Manufacturer_Country', MANUFACTURER, 'Unknown') as [Aircraft Manufacturer Country],

     LONG_NAME as [Aircraft Name Full],

     SHORT_NAME as [Aircraft Name Abbreviated],

     BEGIN_DATE as [Aircraft Begin Date],

     END_DATE as [Aircraft End Date]

FROM

[..\Data Files\CSVs\Aircraft_2010_Update.csv]

(txt, codepage is 1252, embedded labels, delimiter is ';', msq);

[#6]

Looks like I have to read the book twice to fully understand QV.

[Anonymous]

or the "10,000 hour rule".

[#8]

10,000 hour rule ???

[Anonymous]

Wikipedia Source:

Outliers: The Story of Success Throughout the publication, Gladwell repeatedly mentions the "10,000-Hour Rule", claiming that the key to success in any field is, to a large extent, a matter of practicing a specific task for a total of around 10,000 hours.

[#10]

Thanks. Never heard of this before. Seems that the author pulled this 10,000 out of (insert word here). 100 hours might be enough for folks like these - http://en.wikipedia.org/wiki/Richard_Feynman