3 Replies Latest reply: Oct 17, 2016 2:58 PM by Seth Rosenbauer RSS

    Synthetic Keys

    Seth Rosenbauer

      I have a script that looks something like below

       

      sub Transform(vMinDay, vMaxDay)

      FOR i = vMinDay to vMaxDay

       

           INDEX:

           LOAD

           A,

           B,

           IF(LEN(TRIM(C)) > 0, C, '<BLANK>') AS [REPORTING C],

           D

           FROM...$(i)

       

           LEFT JOIN(INDEX)

           LOAD,

           A,

           E,

           F,

           J

           FROM...$(i)

       

           LEFT JOIN(INDEX)

           LOAD

           A,

           K,

           L,

           M

           FROM...$(i)

      NEXT

      END SUB

      CALL Transform(1,2)

       

       

      I'm getting synthetic keys for every field that I create in the first load of INDEX. I bolded the fields I get synthetic keys for. For example, I get synthetic keys for the following fields: A, B, [REPORTING C] and D.

       

      Thoughts anyone?

        • Re: Synthetic Keys
          Marco Wedel

          after the joins your INDEX table has different fields from the initial load, thus subsequent loads will not be autoconcatenated to the INDEX table.

          Try with Concatenate(INDEX) preceding the INDEX LOAD.

          You might have to define it in a variable after the first load to avoid an error.

          Edit: would not work

           

          hope this helps

           

          regards

           

          Marco

          • Re: Synthetic Keys
            Frank Dehner

            The problem is the loop! In first run the join will work as expected by key-field "A". In the next run the other fields are present too and join will take them also as key. So your join result is different from (correct) first run.

            One possible workaround is to load the partial data to be joined into temp tables inside the loop and join them outside the loop. Try something like this:

            (By the way: As you can see I prefer transparency for the kind of load to ensure what happens really.)

             

            sub Transform(vMinDay, vMaxDay)

            INDEX:

            NoConcatenate LOAD * INLINE [A, B, 'REPORTING C', D];

            _tmp_EFJ:

            NoConcatenate LOAD * INLINE [A, E, F, J];

            _tmp_KLM:

            NoConcatenate LOAD * INLINE [A, K, L, M];

            FOR i = vMinDay to vMaxDay

                 Concatenate(INDEX)

                 LOAD

                 A,

                 B,

                 IF(LEN(TRIM(C)) > 0, C, '<BLANK>') AS [REPORTING C],

                 D

                 FROM...$(i)

             

                 Concatenate(_tmp_EFJ)

                 LOAD

                 A,

                 E,

                 F,

                 J

                 FROM...$(i)

             

                 Concatenate(_tmp_KLM)

                 LOAD

                 A,

                 K,

                 L,

                 M

                 FROM...$(i)

            NEXT

            JOIN(INDEX) LOAD

                 //key

                 A,

                 //new values

                 E,F,J

            Resident _tmp_EFJ;

            JOIN(INDEX) LOAD

                 //key

                 A,

                 //new values

                 K,L,M

            Resident _tmp_KLM;

            Drop Tables _tmp_KLM, _tmp_EFJ;

            END SUB

            CALL Transform(1,2)