9 Replies Latest reply: Sep 6, 2011 12:26 PM by Donald Hutchins RSS

    Expression based mapping

      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

          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

              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

                  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:

                   

                  Do

                    ...

                  Loop While {condition}

                    • Re: Expression based mapping

                      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

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

                          • Expression based mapping

                            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

                                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

                                    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

                                        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.