3 Replies Latest reply: Nov 24, 2016 11:01 AM by Ishu Mehta RSS

    How to make new table with fields name coming from rows of an existing table?

    Ishu Mehta

      Existing Table 1 -  "dimensionTable"

      nameColumn typedataType
      ga:landingPageDIMENSIONSTRING
      ga:sessionsMETRICINTEGER
      ga:pageViewsMETRICINTEGER

       

       

      Existing Table 2 - "dimensionValue"

       

      data
      /home
      1
      3
      /home/login
      2
      5
      /home/myAccount
      2
      6
      /search
      6
      23
      /contactUs
      4
      22

       

      How can I achieve the below mentioned table using above tables with the help of data load editor ?
      I got the above mentioned data from the google analytics API in JSON and the QLIK Rest connector has created these above mentioned tables.

       

      Desired Table - "MasterTable"

       

      ga:landingPagega:sessionsga:pageViews
      /home13
      /home/login25
      /home/myAccount26
      /search623
      /contactUs422
        • Re: How to make new table with fields name coming from rows of an existing table?
          Thomas Le Gall

          Here you go, free of charge:

           

          // here your first table

          Fields:

          LOAD

              name

          FROM [lib://Test/Tests_Community.xlsx]

          (ooxml, embedded labels, table is Parameter);

           

          // here your second table

          Data:

          LOAD

              data,

              recno() as Line

          FROM [lib://Test/Tests_Community.xlsx]

          (ooxml, embedded labels, table is Values);

           

          for i=0 to noofrows('Fields')-1

           

              let vFieldName=peek('name',i,'Fields');

             

            [Data_$(vFieldName)]:

               LOAD *,

              recno() as LineNumber;

            LOAD

              data as [$(vFieldName)]

              resident Data

              where floor((Line-($(i)+1))/noofrows('Fields'))=(Line-($(i)+1))/noofrows('Fields');

                

          next i

           

          for i=0 to noofrows('Fields')-1

           

            let vFieldName=peek('name',i,'Fields');

             

              if $(i)=0 then

              let vAppelTable='noconcatenate FinalTable:';

              else

              let vAppelTable='left join(FinalTable)';

              endif

             

              $(vAppelTable)

              LOAD *

              resident [Data_$(vFieldName)];

             

              Drop table [Data_$(vFieldName)];   

             

          next i

           

          Drop tables Fields, Data;

          Drop field LineNumber;

          • Re: How to make new table with fields name coming from rows of an existing table?
            Thomas Le Gall

            Even better with only this:

             

            Fields:

            LOAD

                name

            FROM [lib://Test/Tests_Community.xlsx]

            (ooxml, embedded labels, table is Parameter);

             

            Data:

            LOAD

                data,

                recno() as Line

            FROM [lib://Test/Tests_Community.xlsx]

            (ooxml, embedded labels, table is Values);

             

            for i=0 to noofrows('Fields')-1

             

                let vFieldName=peek('name',i,'Fields');

               

                    if $(i)=0 then

                let vAppelTable='noconcatenate FinalTable:';

                else

                let vAppelTable='left join(FinalTable)';

                endif

               

              $(vAppelTable)

                LOAD *;

                LOAD *,

                recno() as LineNumber;

              LOAD

                data as [$(vFieldName)]

                resident Data

                where floor((Line-($(i)+1))/noofrows('Fields'))=(Line-($(i)+1))/noofrows('Fields');

                  

            next i

             

            Drop tables Fields, Data;

            Drop field LineNumber;