6 Replies Latest reply: Aug 7, 2014 3:13 AM by Tanja Gonzalez RSS

    Generic load - does not create the new table

    Tanja Gonzalez

      Hello,

       

      I have detected  a bug in a Generic LOAD statement, and I haven't seen other discussions on this matter.

       

      I have a table which contains columns with values (LIBELLEHIERARCHIE1) and columns with column names (LIBELLETYPEHIERARCHIE1).

      I need to transform the table in order to create columns with the correct names (LIBELLETYPEHIERARCHIE1) containing rows with values.

      I have 6 columns of this type.

      To do this I use 6 times the Generic LOAD statement.

       

      Here is my code :

       

      // Creation of a giant table containing 6 columns of values and 6 columns of column names.

       

      Directory;

      GenericHIERARCHIEWBS02 :

      LOAD IDWBS,

           Lookup('CODENATOPE', 'IDNATOPE', IDNATOPE, 'NATOPE02') as CODENATOPE,

           Lookup('LIBELLENATOPE', 'IDNATOPE', IDNATOPE, 'NATOPE02') as LIBELLENATOPE, 

           ApplyMap('LIBELLEHIERARCHIE02', HIERARCHIE_1, 'SANS HIERARCHIE') as LIBELLEHIERARCHIE1,

           ApplyMap('LIBELLEHIERARCHIE02', HIERARCHIE_2, 'SANS HIERARCHIE') as LIBELLEHIERARCHIE2,

           ApplyMap('LIBELLEHIERARCHIE02', HIERARCHIE_3, 'SANS HIERARCHIE') as LIBELLEHIERARCHIE3,

           ApplyMap('LIBELLEHIERARCHIE02', HIERARCHIE_4, 'SANS HIERARCHIE') as LIBELLEHIERARCHIE4,

           ApplyMap('LIBELLEHIERARCHIE02', HIERARCHIE_5, 'SANS HIERARCHIE') as LIBELLEHIERARCHIE5,

           ApplyMap('LIBELLEHIERARCHIE02', HIERARCHIE_6, 'SANS HIERARCHIE') as LIBELLEHIERARCHIE6,

           If(not IsNull(ApplyMap('IDHIERARCHIE02', HIERARCHIE_1)), ApplyMap('IDHIERARCHIE02', HIERARCHIE_1)) as LIBELLETYPEHIERARCHIE1,

           If(not IsNull(ApplyMap('IDHIERARCHIE02', HIERARCHIE_2)), ApplyMap('IDHIERARCHIE02', HIERARCHIE_2)) as LIBELLETYPEHIERARCHIE2,

           If(not IsNull(ApplyMap('IDHIERARCHIE02', HIERARCHIE_3)), ApplyMap('IDHIERARCHIE02', HIERARCHIE_3)) as LIBELLETYPEHIERARCHIE3,

           If(not IsNull(ApplyMap('IDHIERARCHIE02', HIERARCHIE_4)), ApplyMap('IDHIERARCHIE02', HIERARCHIE_4)) as LIBELLETYPEHIERARCHIE4,

           If(not IsNull(ApplyMap('IDHIERARCHIE02', HIERARCHIE_5)), ApplyMap('IDHIERARCHIE02', HIERARCHIE_5)) as LIBELLETYPEHIERARCHIE5,

           If(not IsNull(ApplyMap('IDHIERARCHIE02', HIERARCHIE_6)), ApplyMap('IDHIERARCHIE02', HIERARCHIE_6)) as LIBELLETYPEHIERARCHIE6          

      FROM

      [$(RepertoireQVDBRUT)HIERARCHIEWBS01.qvd]

      (qvd);

       

      // Creation of 6 sub tables. Each table contains the key made of 3 attributes (IDWBS, CODENATOPE, LIBELLENATOPE)

      // the column name (LIBELLETYPEHIERARCHIE1) and the column values (LIBELLEHIERARCHIE1)

       

      GenericSubTableHIERARCHIEWBS02 :

      Generic LOAD IDWBS, CODENATOPE, LIBELLENATOPE, LIBELLETYPEHIERARCHIE1, LIBELLEHIERARCHIE1 Resident GenericHIERARCHIEWBS02;

      GenericSubTableHIERARCHIEWBS02 :

      Generic LOAD IDWBS, CODENATOPE, LIBELLENATOPE, LIBELLETYPEHIERARCHIE1, LIBELLEHIERARCHIE1 Resident GenericHIERARCHIEWBS02;

      GenericSubTableHIERARCHIEWBS02 :

      Generic LOAD IDWBS, CODENATOPE, LIBELLENATOPE, LIBELLETYPEHIERARCHIE2, LIBELLEHIERARCHIE2 Resident GenericHIERARCHIEWBS02;

      GenericSubTableHIERARCHIEWBS02 :

      Generic LOAD IDWBS, CODENATOPE, LIBELLENATOPE, LIBELLETYPEHIERARCHIE3, LIBELLEHIERARCHIE3 Resident GenericHIERARCHIEWBS02;

      GenericSubTableHIERARCHIEWBS02 :

      Generic LOAD IDWBS, CODENATOPE, LIBELLENATOPE, LIBELLETYPEHIERARCHIE4, LIBELLEHIERARCHIE4 Resident GenericHIERARCHIEWBS02;

      GenericSubTableHIERARCHIEWBS02 :

      Generic LOAD IDWBS, CODENATOPE, LIBELLENATOPE, LIBELLETYPEHIERARCHIE5, LIBELLEHIERARCHIE5 Resident GenericHIERARCHIEWBS02;

      GenericSubTableHIERARCHIEWBS02 :

      Generic LOAD IDWBS, CODENATOPE, LIBELLENATOPE, LIBELLETYPEHIERARCHIE6, LIBELLEHIERARCHIE6 Resident GenericHIERARCHIEWBS02;

       

      The code in red is 2 times exactly the same statement. If I write the statement only one time, the table is not created.

      OK, my code works like this, but I'm wondering if I have done something wrong or is there a bug in the Generic LOAD statement ?

       

      Thanks in advance for your answers.

       

      Tanja

        • Re: Generic load - does not create the new table
          Evan Kurowski

          First, looking over the syntax, you could probably shorten all 6 repetitions of the following:

           

          FROM: If(not IsNull(ApplyMap('IDHIERARCHIE02', HIERARCHIE_1)), ApplyMap('IDHIERARCHIE02', HIERARCHIE_1)) as LIBELLETYPEHIERARCHIE1,

           

          TO: ApplyMap('IDHIERARCHIE02', HIERARCHIE_1, Null()) AS LIBELLETYPEHIERARCHIE1,

           

          Then, if you are making 6 passes at attaching a single field to a 3-field compound key, maybe using GENERIC isn't necessary.

          [KEYS]: NOCONCATENATE LOAD IDWBS, DISTINCT, CODENATOPE RESIDENT [GenericHIERARCHIEWBS02];
          FOR i = 1 to 6   
          Let vCurrentFieldName = PEEK('GenericHIERARCHIEWBS02',0,'LIBELLETYPEHIERARCHIE$(i)');
          LEFT JOIN (KEYS)
               LOAD IDWBS, DISTINCT, CODENATOPE,     [LIBELLEHIERARCHIE$(i)] AS [$(vCurrentFieldName)] RESIDENT      [GenericHIERARCHIEWBS02];
          NEXT   

           

          Or even if you keep the generic go to:

          [GenericSubTableHIERARCHIEWBS02]:
          FOR i = 1 to 6
               Generic LOAD IDWBS, CODENATOPE, LIBELLENATOPE, [LIBELLETYPEHIERARCHIE$(i)], [LIBELLEHIERARCHIE$(i)]      Resident GenericHIERARCHIEWBS02;
          NEXT

           

          But if you have a significant amount of field names packed into LIBELLETYPEHIERARCHIE1 - 6, then I expect your result is a pretty significant star schema.

            • Re: Generic load - does not create the new table
              Tanja Gonzalez

              Hello Evan,

              Thank you for your reply. It was very helpful and it simplified my script.

               

              I tried the solution :

              HIERARCHIEWBS02:

              NOCONCATENATE LOAD IDWBS, CODENATOPE, LIBELLENATOPE RESIDENT GenericHIERARCHIEWBS02;

              FOR i = 1 to 6 

              FOR j = 0 to NoOfRows('GenericHIERARCHIEWBS02')-1

              Let vCurrentFieldName = PEEK('LIBELLETYPEHIERARCHIE$(i)', j, 'GenericHIERARCHIEWBS02');

              IF not IsNull($(vCurrentFieldName)) then

              EXIT For

              ENDIF

              NEXT

               

              TRACE vCurrentFieldName $(vCurrentFieldName);

              LEFT JOIN

                   LOAD IDWBS, CODENATOPE, LIBELLENATOPE, [LIBELLEHIERARCHIE$(i)] AS [$(vCurrentFieldName)] RESIDENT GenericHIERARCHIEWBS02;

              NEXT   


              I needed to add the 2nd FOR loop, because sometimes the value of the label is NULL.

              But when I recharged the script, it blocked during the 2nd passage through the loop without any error message (it is maybe space consuming and my computer can't handle it)...

               

              So I tried the "generic" solution :

              FOR i = 1 to 6

              GenericSubTableHIERARCHIEWBS02 :

                   Generic LOAD IDWBS, CODENATOPE, LIBELLENATOPE, [LIBELLETYPEHIERARCHIE$(i)], [LIBELLEHIERARCHIE$(i)]      Resident GenericHIERARCHIEWBS02;

              NEXT

              and it worked OK.

                • Re: Re: Generic load - does not create the new table
                  Evan Kurowski

                  Hello Tanja,
                  I tested a simulation of the LEFT JOIN method and perhaps a component that was missing was a WHERE clause that drops all join entries where the target field is null.  (i.e. WHERE Len(Trim([LIBELLEHIERARCHIE$(I)])) > 0  )


                  You can see my simulation and results in the image below:

                  When I don't use the aforementioned WHERE clause, my example creates 64 rows of data (with plenty of null pockets), however when the WHERE clause is activated, it drops all rows where the target field is blank and the load produces one single "unified" row, kind of "accordion" collapsing the data spread out over the original table.

                   

                  20140805_squashing_table.png

                    • Re: Re: Generic load - does not create the new table
                      Tanja Gonzalez

                      Hello Evan,

                       

                      I have tried out your code by adjusting it a little bit and it works :


                      //load the new table HIERARCHIEWBS02

                      HIERARCHIEWBS02: 

                      NoConcatenate LOAD Distinct IDWBS, CODENATOPE, LIBELLENATOPE RESIDENT GenericHIERARCHIEWBS02;

                      LOAD Distinct IDWBS, CODENATOPE, LIBELLENATOPE RESIDENT GenericHIERARCHIEWBS02;


                      FOR i = 1 to 6 

                       

                      // a loop to read the column names (F1 to F6)

                      FOR j = 0 to NoOfRows('GenericHIERARCHIEWBS02')-1

                      Let vCurrentFieldName = PEEK('LIBELLETYPEHIERARCHIE$(i)', j, 'GenericHIERARCHIEWBS02');

                      IF not IsNull($(vCurrentFieldName)) then

                      EXIT For

                      ENDIF

                      NEXT

                       

                      LEFT JOIN (HIERARCHIEWBS02)

                           LOAD IDWBS, CODENATOPE, LIBELLENATOPE, LIBELLEHIERARCHIE$(i) as $(vCurrentFieldName) RESIDENT GenericHIERARCHIEWBS02

                           Where Len(Trim([LIBELLEHIERARCHIE$(i)])) > 0;

                      NEXT   

                       

                      And here is the result :

                      Table result.jpg

                      As you can see, at the beginning of the code, I still need to LOAD the table HIERARCHIEWBS02 twice.

                      I have tried by changing the attribute names and put the NoConcatenate, but the new table still isn't being created.

                      So at least now se know that this is not related to the generic tables.