5 Replies Latest reply: Aug 12, 2011 9:49 AM by Klaus Skalts RSS

    Help with crosstable wizzard

      Hello,

      I want to link more than one column from an Excel sheet to a sql db. I've managed to link one column but when I load the second field and rename it the script stops loading and give a script error. My code so far:

      CrossTable(Contractgroup, data)

      LOAD Costcenter as costcabroad,

      [1VG won] as [1VG],

      [2VG won] as [2VG],

      [3VG won] as [3VG],

      [4VG won] as [4VG],

      [5VG won] as [5VG],

      [6VG won] as [6VG],

      [7VG won] as [7VG],

      [4LG won] as [4LG],

      [7LG won] as [7LG],

      [6GGZ_C won] as [6GGZ C]

      FROM

      D:\Qlikview9\Testbestanden\begroting.xls

      (biff, embedded labels, table is dagen$);

      The troubles begin with the field Costcenter. When I load it without renaming it, it loads without an error. I have to rename it to make it link to the field costcabroad from the sql db but the the script stops loading.

      Anyone?

        • Help with crosstable wizzard

          Hi Richard,

          Your code seems to be correct , what are the fields you are linking between this excel code and sql db? May be there could be something wrong in the linking field.What is the error message you are having?

          Below my sample code works fine for me.

           

          CROSSTABLE(BudYear, BudAmount)
          LOAD Office as OfficeID,
          [2003] as FY03,
          [2004] as FY04,
          [2005] as FY05,
          [2006] as FY06,
          [2007] as FY07
          FROM Budget.xls (biff, header is line, embedded labels, table is Sheet1$);

          Regards,

          Silvia

            • Help with crosstable wizzard

              The script loads and at the end I get a message: (dutch language)

               

              When I translate it for you it tells that the execution of the script fails and then it asks if it should load the old data again.

              The weird thing is, that I only get this message if I rename the field Costcenter to costcabroad

               

              I use the costcabroad field above for other things and now I want to match them. The rest loads fine (contractgroupdate & data)

               

               

                • Help with crosstable wizzard

                  Hi,

                  It might be due to "Circular Reference", pls check you field links.

                  Silvia

                   

                    • Help with crosstable wizzard

                      I've removed a circular reference but it still does not work. My full code: (as you can see I want to match/link the field costcabroad) What am I doing wrong?

                       

                      LOAD

                      "Afd_Extra1",

                      IF(Trim(costcTemp)= '', '40000',costcTemp) as costcabroad,

                      Sector,

                      "Sector_ID",

                       

                      sql select

                      "Afd_Extra1",

                      KostenplaatsNummer as costcTemp,

                      Sector,

                      "Sector_ID"

                       

                      From …..db;

                       

                       

                      CrossTable(Contractgroup, data)

                      LOAD Costcenter as costcabroad,

                      [1VG won] as [1VG],

                      [2VG won] as [2VG],

                      [3VG won] as [3VG],

                      [4VG won] as [4VG],

                      [5VG won] as [5VG],

                      [6VG won] as [6VG],

                      [7VG won] as [7VG],

                      [4LG won] as [4LG],

                      [7LG won] as [7LG],

                      [6GGZ_C won] as [6GGZ C]

                      FROM

                      D:\Qlikview9\Testbestanden\begroting.xls

                      (biff, embedded labels, table is dagen$);

                       

                    • Help with crosstable wizzard
                      Klaus Skalts

                      I might be the QlikView version you are using ..... a some point QlikView could not handle Circular References and just crashed without much information.

                       

                      So I think that you should try to see if your are creating circular references .....