topic Expression based mapping in QlikView

Expression based mapping

Thu, 25 Aug 2011 02:12:30 GMT

I am new to the community and to Qlickview (less than two weeks).

I assume my question might be in the expression category as well, but based on my understanding since what I am asking about will take place each load, it should be a scripting question ... ?

The challenge is to create a roll up hierarchy based on combination of several columns from a single table.

The hierarchy is based on the combination of product number and product family columns (in Business Objects report this hierarchy is created via if statement driven variables)

ApplyMapp – based on my understanding of the manual, replaces the values in the existing fields based on the given expression, however, my requirement is to have additional columns – levels of hierarchies, thus I need to add columns with values of the fields in these columns being assigned via expression instead of changing the field values in the existing columns.

Is there a mode in which ApplyMap creates a new column in the existing table instead of over-writing the existing values in the column?

If ApplyMap is not the proper tool, should there a be a QVD created, if yes, should the hierarchy columns be created via a variable/LET function script ?

IF QVD is not a correct approach should Inline Data Wizzard be applied in this case?

Thank you in advance,

Michael.

Expression based mapping

Thu, 25 Aug 2011 02:35:18 GMT

Assuming you have columns for ProdNumber and ProdFamily, and various combinations of those result in a Hierarchy Value, you can indeed use ApplyMap. And you'll be creating new columns in the process (not replacing any existing columns).

In your script, you could setup the Map something like the following. This assumes you have products P1, P2 and Family Fam1, Fam2, with combinations mapping to groups called H1, H2, or H3, etc.

MapProdHierarchy:
MAPPING LOAD * INLINE [
ProdNumber_ProdFamily, HierarchyGroup
P1_Fam1, H1
P2_Fam1, H2
P1_Fam2, H3
];

Then, in the LOAD script for the table, you'd use the map as follows to create a new column called GroupName:

MyTable:
LOAD
ProdNumber,
ProdFamily,
ApplyMap('MapProdHierarchy', ProdNumber & '_' & ProdFamily ) AS GroupName
*
FROM MyFile.qvd (qvd);

You could of course load the map from an external source if desired... I loaded inline here for simplicity. Also, if you have tons of groups, you should consider a Join (may offer improved performace over ApplyMap).

Give it a try, and if you have trouble post up a sample of your QVW and I'll be happy to help.

Re: Expression based mapping

Mon, 29 Aug 2011 20:58:47 GMT

Hi DHutchins,

Thank you for the reply. The mapping is a bit ... convoluted, so the H groups in your sample below will have to be created with an expression. Also in BO this expressions are based on the InList function to map multiply product numbers to single product family names. I am going through the manuals and don’t anything equivalent to InList function. Can a While Clause handle multiple arguments in QlikView?

Thank you again.

Expression based mapping

Tue, 30 Aug 2011 14:28:27 GMT

As for InList(), you may want to have a look at the QlikView match() function (and also: mixmatch, wildmatch). The match() function allows you to compare a string or field (first argument) to a varible number of strings or string expressions.

As for while, you can use in the context of a LOAD, but this essentially loads multiple iterations, which is probably not what you want. As for a general looping constructs in the script, you have DO WHILE | UNTIL, FOR NEXT, FOR EACH. And you may have have multiple conditions in the do loop.

Do While {condition} AND {condition} etc...

...

Loop

Or you can of course specify conditions on the Loop, so that the loop always executes the first time:

...

Loop While {condition}

Re: Expression based mapping

Wed, 31 Aug 2011 00:16:08 GMT

Hi,

Thank you again for your help, I’ve tried to figure out the proper syntax out, but looks like I cant. Below is my entire script. The error “Syntax error, missing/misplaced FROM:Bookings:” seems to be triggered with the ApplyMap function. This is a watered down version of what I actually have to do, and eventually Mapping Load statement would have to be nested IF statement with Match function. Thank you again.

SET ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

SET MoneyFormat='$#,##0.00;($#,##0.00)';

SET TimeFormat='h:mm:ss TT';

SET DateFormat='M/D/YYYY';

SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

Directory;

SalesRollUpMappings:

Mapping Load * Inline [Product Super Family, Rollup

Product Super Family,(Match(Product Super Family,'Mobility',’Security','Mai',’Suites','Consulting')>0,'ABC','Other')

];

Bookings:

LOAD

     Region,
     Area,
     [Sales Region],
     [Sales Territory],
     [Booked Date],
     [Fiscal Sales Week of Qtr No],
     [Fiscal Week End Date],
     [Fiscal Quarter-Finance Format],
     Organization,
     [Product Super Family],
     [BU Interlock Family],
     [Comp Eligible],
     [Mgmt USD Amount],
     [Current Fiscal Year Plan Amount]

ApplyMap('SalesRollUpMappings','Product Super Family') AS SalesRollUp *

FROM

QlikView Bookings.csv

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

Expression based mapping

Wed, 31 Aug 2011 01:02:15 GMT

You need a comma after [Current Fiscal Year Plan Amount] and also a comma after SalesRollup (before the *).

Expression based mapping

Wed, 31 Aug 2011 01:12:15 GMT

Also, if you are just mapping a field from your CSV called [Product Super Family] as you indicate in the match() function, I don't think you need it. You can instead setup a map like the following:

SalesRollUpMappings:

Mapping Load * Inline [
Product Super Family, Rollup
'Mobility','ABC'
'Security','ABC'
'Mai','ABC'
'Suites','ABC'
'Consulting','ABC'
];

Then you can load it like so:

Bookings:

LOAD
Region,
Area,
[Sales Region],
[Sales Territory],
[Booked Date],
[Fiscal Sales Week of Qtr No],
[Fiscal Week End Date],
[Fiscal Quarter-Finance Format],
Organization,
[Product Super Family],
[BU Interlock Family],
[Comp Eligible],
[Mgmt USD Amount],
[Current Fiscal Year Plan Amount],
ApplyMap('SalesRollUpMappings',[Product Super Family],'Other') AS SalesRollUp,
*
FROM
QlikView Bookings.csv
(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

Expression based mapping

Wed, 31 Aug 2011 01:24:07 GMT

And just to clarify, you really don't have to put the 'quote' delimters around the strings in the Mapping Load. It works fine if you do, but you could also write as:

SalesRollUpMappings:

Mapping Load * Inline [
Product Super Family, Rollup
Mobility,ABC
Security,ABC
Mai,ABC
Suites,ABC
Consulting,ABC
];

Re: Expression based mapping

Fri, 02 Sep 2011 21:37:29 GMT

DHutchins,

Thank you again, the reason I want to use MATCH function is this mapping will eventually be based on several different fields and thus nested IF statements, plus I want to learn as much as I can about syntax and functionality

Thank you for the corrections however both versions - MATCH/Non-match generate exactly the same error, I know that it is the syntax around the PRODUCT SUPER FAMILY, I just cant figure out what.

ERROR:

Field names must be unique within table

LOAD

Region,

Area,

Organization,

ApplyMap('SalesRollUpMappings',[Product Super Family],'other') AS SalesRollUp, *

FROM

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

CODE:

MATCH VERSION

SET ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

SET MoneyFormat='$#,##0.00;($#,##0.00)';

SET TimeFormat='h:mm:ss TT';

SET DateFormat='M/D/YYYY';

SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

Directory;

SalesRollUpMappings:

Mapping Load * Inline [ProductSuperFamily, Rollup

if(Match(Product Super Family,'Endpoint Mgmt and Mobility','Endpoint Security','Mail & Web Security','Security Suites','Symantec Hosted Services - Consulting','Symantec Hosted Services - Security','Symantec Hosted Services - Storage')>0,'ISG','Other')

];

LOAD

Region,

Area,

Organization,

ApplyMap('SalesRollUpMappings',[Product Super Family]) AS SalesRollUp, *

FROM

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

NON-MATCH VERSION:

SET ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

SET MoneyFormat='$#,##0.00;($#,##0.00)';

SET TimeFormat='h:mm:ss TT';

SET DateFormat='M/D/YYYY';

SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

Directory;

SalesRollUpMappings:

Mapping Load * Inline [ProductSuperFamily, Rollup

Endpoint Mgmt and Mobility,ISG

Endpoint Security,ISG

Mail & Web Security,ISG

Security Suites,ISG

Symantec Hosted Services - Consulting,ISG

Symantec Hosted Services - Security,ISG

Symantec Hosted Services - Storage,ISG];

LOAD

Region,

Area,

Organization,

ApplyMap('SalesRollUpMappings',[Product Super Family],'other') AS SalesRollUp, *

FROM

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

Thank you again !

Re: Expression based mapping

Tue, 06 Sep 2011 16:26:32 GMT

As for your error on "field names must be unique", get rid of the "*" as your last column. If you are loading columns explicitly as in this case, then you just name the columns in the LOAD that you want. When you add the "*", you are effectively saying you want ALL columns, so if you've already named some of them, you get the duplicate. So use "*" only when you want ALL of the columns in the load source, and only when you haven't already named one or more of them.

On using Match(), you can of course just specify the formula as a column in your Load script. This would create a new column named 'Rollup' based on the Match() evaluation: (here I load from a RESIDENT table just for my testing, of course you load from your CSV)

Bookings:
LOAD 
Region, 
Area,
if(Match([Product Super Family],'Endpoint Mgmt and Mobility','Endpoint Security','Mail & Web Security','etc...')>0,'ISG','Other') As Rollup
RESIDENT BookingSource;

Note that the mapping table need not be INLINE via script, but may be loaded from an external source (Excel, database, etc.) ... and nothing prevents you from building htis table via script too. That syntax could look like the following:

SalesRollUpMappings:

Mapping Load * Inline [

ProductSuperFamily, Rollup

Endpoint Mgmt and Mobility,ISG

Endpoint Security,ISG

Mail & Web Security,ISG

Security Suites,ISG

Symantec Hosted Services - Consulting,ISG

Symantec Hosted Services - Security,ISG

Symantec Hosted Services - Storage,ISG

];

Bookings:
LOAD
Region,
Area,
ApplyMap('SalesRollUpMappings',[Product Super Family],'Other') AS SalesRollUp
RESIDENT BookingSource;

If your logic is complex or conditional based on other columns or values, you may need to handle that complexity in a step prior to the final load mapping (by creating your mapping table beforehand via script).

Note also you can certain consider just JOINing the data to a related table in your database or data source.