13 Replies Latest reply: Feb 10, 2015 11:48 AM by Laura Sébille RSS

    Applymap not working

    Laura Sébille

      Hey everyone,

       

      Here is my code :

       

      HR_EMPLOYEES:

      mapping load

      ATTRIBUTE5,

      EMPLOYEE_ID

      from HR_EMPLOYEES.qvd(qvd);

       

      test:

      LOAD

      RESOURCENAME as ATTRIBUTE5,

      Applymap('HR_EMPLOYEES', RESOURCENAME) as employee_id,

      EVENTCODEID,

      EVENTDATETIME as Event_shift_time

      from OtherTable.qvd (qvd);

       

       

      In the output of table "test", the "employee_id" field is supposed to contain the values of EMPLOYEE_ID (in HR_EMPLOYEES), but it doesn't, the field employee_id contains the same values as ATTRIBUTE5.

       

      I really don't get why :/

       

      Any idea?

       

      thank you for your help

      Laura

        • Re: Applymap not working
          Rudolf Linder

          please check if the values of ATTRIBUTE5 from HR_EMPLOYEES and RESOURCENAME from othertable

          are identical. the behaviour described would result if no match is found. maybe different upper/lower case?

          if number different number format??

          • Re: Applymap not working
            Jared Papador

            Since you didnt specify a default value in your apply map statement, it is just returning RESOURCENAME from the test table.

             

            It doesnt look like it is finding any matches between ATTRIBUTE5 and RESOURCENAME.  Are they in the same format?

            • Re: Applymap not working
              Ruben Marin

              Hi Laura, ApplyMap() keeps the value of the ValueToSearch if this doesn't exist in the Map table, I would load the values of HR_EMPLOYEES in an isolated table (unique field names, ie adding MAP_ prefix to the fields name) and check if there is the same value for MAP_ATTRIBUTE5 and ATTRIBUTE5 (no blank spaces or anything different).

                • Re: Applymap not working
                  Peter Cammaert

                  Or temprarily remove the MAPPING prefix from your code. You'll get an additional table that uses ATTRIBUTE5 as a key to connects EMPLOYEE_ID to the test table.

                   

                  Use a Table box to check wether every EVENTCODEID has an ATTRIBUTE5 value and a corresponding EMPLOYEE_ID.

                • Re: Applymap not working
                  Priyanka Rao

                  Hi Laura,

                   

                  The technique used for ApplyMap seems to be correct.

                  Just check the values in 'ATTRIBUTE5' matches with 'RESOURCENAME'.

                  Also, give the thhird parameter of the ApplyMap with a default vale.

                   

                  Hope it helps!

                  Cheers !

                  • Re: Applymap not working
                    Nick Hoff

                    Try the statement below and if you are getting Invalid for each field the match isn't being met.  If that is the case can you provide a table box of two fields to see why the match isn't being made?

                     

                    HR_EMPLOYEES:

                    MAPPING LOAD

                    EMPLOYEE_ID,

                    ATTRIBUTE5

                    FROM HR_EMPLOYEES.qvd(qvd);

                     

                    TEST:

                    LOAD

                    RESOURCENAME as ATTRIBUTE5,

                    APPLYMAP('HR_EMPLOYEES', RESOURCENAME,'Invalid') AS EMPLOYEE_ID,

                    EVENTCODEID,

                    EVENTDATETIME as Event_shift_time

                    from OtherTable.qvd (qvd);

                    • Re: Applymap not working
                      Laura Sébille

                      you are right, I've put a default value in the applymap and it always returns this value.

                       

                       

                      That's very weird because I 've tried :

                       

                      HR_EMPLOYEES:

                      load

                      ATTRIBUTE5,

                      EMPLOYEE_ID

                      from HR_EMPLOYEES.qvd(qvd);

                       

                       

                      test:

                      LOAD

                      RESOURCENAME as ATTRIBUTE5,

                      EVENTCODEID,

                      EVENTDATETIME as Event_shift_time

                      from OtherTable.qvd (qvd);

                       

                      So I do get 2 tables linked on ATTRIBUTE5 and then,  I do have an EMPLOYEE_ID for each ATTRIBUTE5...

                       

                      I use an old version of Qlikview (9), might this be the reason ?

                       

                      Thank you again

                        • Re: Applymap not working
                          Jared Papador

                          Is EMPLOYEE_ID and ATTRIBUTE5 a 1:1 relationship.  Maps only work correctly for a 1:1 relationship while just creating a link between the tables would work for 1:n relationships.

                           

                          You could try doing a left join instead of a map and see if that produces values:

                           

                           

                          test:

                          LOAD

                          RESOURCENAME as ATTRIBUTE5,

                          EVENTCODEID,

                          EVENTDATETIME as Event_shift_time

                          from OtherTable.qvd (qvd);

                           

                          Left Join (test)

                          load

                          ATTRIBUTE5,

                          EMPLOYEE_ID as newemployeeid

                          from HR_EMPLOYEES.qvd(qvd);

                          • Re: Applymap not working
                            Ruben Marin

                            Hi Laura, with this last test you've done you can try to create a table box with EMPLOYEE_ID and EVENTCODEID, if both values are in the same row, then a linked value exist, otherwise HR_EMPLOYEES.ATTRIBUTE5 is different than test.ATTRIBUTE5.

                             

                            If can upload a sample we can get you a direct solution instead of guiding tests.

                          • Re: Applymap not working
                            Laura Sébille

                            Ok, I have resolved my first trouble, now I have another applymap which is not working :

                             

                            Code_event_tl:

                            mapping LOAD

                            EVENTCODEID,

                            EVENTCODENAME

                            from EVENT_CODE.qvd (qvd)

                            ;

                             

                            test:

                            LOAD

                            RESOURCENAME as ATTRIBUTE5,

                            Applymap('HR_EMPLOYEES', RESOURCENAME, 'null') as EMPLOYEE_ID,

                            Applymap('Code_event_tl',EVENTCODEID, 'null') as Event_shift,

                            EVENTCODEID,

                            EVENTDATETIME as Event_shift_time

                            from OtherTable.qvd (qvd);

                             

                            This is the "Event_shift" value which always returns null... whereas the Code_event_tl table is a simple table with 7 rows.

                             

                            I also notice that when I look at the "schema view" ( where you can see the tables and the relationships), there is a lonely table called "$orphan_EVENTCODENAME" and it contains EVENTCODENAME.

                             

                            What is that ?

                             

                            Thanks again

                            Laura