topic Re: Book - Qlikview 11 for Developers - Using MAPPING tables in QlikView

Book - Qlikview 11 for Developers - Using MAPPING tables

Thu, 20 Mar 2014 22:03:29 GMT

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);

Re: Book - Qlikview 11 for Developers - Using MAPPING tables

ngulliver — Fri, 21 Mar 2014 10:39:36 GMT

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

Re: Book - Qlikview 11 for Developers - Using MAPPING tables

Fri, 21 Mar 2014 18:04:31 GMT

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

Re: Book - Qlikview 11 for Developers - Using MAPPING tables

jaimeaguilar — Sat, 29 Mar 2014 00:15:16 GMT

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

Re: Book - Qlikview 11 for Developers - Using MAPPING tables

Anonymous — Sat, 29 Mar 2014 01:50:59 GMT

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);

Re: Book - Qlikview 11 for Developers - Using MAPPING tables

Tue, 01 Apr 2014 00:29:29 GMT

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

Re: Book - Qlikview 11 for Developers - Using MAPPING tables

Anonymous — Tue, 01 Apr 2014 00:33:40 GMT

or the "10,000 hour rule".

Re: Book - Qlikview 11 for Developers - Using MAPPING tables

Tue, 01 Apr 2014 00:38:10 GMT

10,000 hour rule ???

Re: Book - Qlikview 11 for Developers - Using MAPPING tables

Anonymous — Tue, 01 Apr 2014 00:41:47 GMT

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.

Re: Book - Qlikview 11 for Developers - Using MAPPING tables

Tue, 01 Apr 2014 22:57:38 GMT

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