4 Replies Latest reply: Aug 18, 2011 9:23 AM by Andreas Dippel RSS

    Adding a variable in crosstable load

      hello community,

       

      i was trying to add a variable (1 AS Choice) to a crosstable loaded excel file

       

      Directory;

      CrossTable (Name, Data, 2)

      LOAD Date,

      etc.

      // Problem starts here because of "crosstable"

      1 AS Choice

      FROM [C:\file.xls]

       

      (biff, embedded labels, header is 1 lines, table is Table1$);

       

      "Choice" is simply added to "Name", Like Adam, Bernard, Charly, Choice

       

      Is there another way, to add a variable while loading with crosstable ?

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

        • Adding a variable in crosstable load
          Anand Chouhan

          Hi,

           

          In this type of table creation(Crosstable) try to create Resident table of that cross table.

          CrossTable (Name, Data, 2)

           

          Temp:

          LOAD Date,

          etc.

          // Problem starts here because of "crosstable"

          1 AS Choice

          FROM [C:\file.xls]  

          (biff, embedded labels, header is 1 lines, table is Table1$);

           

          Resident_Table:

          Load

               Date,

               1 as choice,

                etc

          Resident Temp;

           

          Drop Table Temp;

           

           

          Hope this helps you.

           

          Regards,

          Anand

          • Adding a variable in crosstable load
            Jagan Nalla

            Directory;

             

            CrossTable (Name, Data, 2)

            LOAD

            1 as Choice

            Date,

            Date1

             

            FROM [C:\file.xls]

             

             

            (biff, embedded labels, header is 1 lines, table is Table1$);

             

            - Try this may be it helps you...

              • Adding a variable in crosstable load

                @Anand

                because of the resident load inside qlickview (which is quite fast), this uses not so much cpu time as i expected.

                but it uses a lot of ram, due to the double amount of data (500T+ datasets).

                 

                in case, someone has the same problem, here is how i solved this:

                this can be handled by "pumping" the data month by month, so the amount of redundant datasets is reduced.

                like:

                 

                load data in "loader_table",

                resident  load + variables in "temp_a",

                variables loaded as %name_a,

                drop "loader_table",

                 

                target table

                load * (as %name)

                resident temp_a

                 

                drop temp_a

                load next month

                 

                and so on

                 

                problem solved. thank you