10 Replies Latest reply: Feb 20, 2017 11:14 AM by Royce Tang RSS

    concatenate issue or bug with qlikview ?

    Royce Tang

      Dear experts,

       

      I am not sure if this is a bug with qlikview or there is an error with my syntax. I get this queer error "Table not found error Table not found NoConcatenate qlikview" when I use an alias, the issue in the script i woukld like to point out will be in BOLD.

       

      in the table "temp2" under BDF_Division if i were to change the script to an alias such as F4 or hello, anything else the script works perfectly however it just does not work with BDF_Divison. I have tried changing the field name directly from excel and seem to get no improvement in the results.

       

      another weird thing is that the error does not seem to happen to the other temp tables but ONLY for the table which comes after the first

       

      this is my script:

       

      SET ThousandSep=',';

      SET DecimalSep='.';

      SET MoneyThousandSep=',';

      SET MoneyDecimalSep='.';

      SET MoneyFormat='$#,##0.00;($#,##0.00)';

      SET TimeFormat='h:mm:ss TT';

      SET DateFormat='M/D/YYYY';

      SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';

      SET FirstWeekDay=6;

      SET BrokenWeeks=1;

      SET ReferenceDay=0;

      SET FirstMonthOfYear=1;

      SET CollationLocale='en-US';

      SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

      SET LongMonthNames='January;February;March;April;May;June;July;August;September;October;November;December';

      SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

      SET LongDayNames='Monday;Tuesday;Wednesday;Thursday;Friday;Saturday;Sunday';

       

      t1:

      CrossTable(date,cash,6)

      LOAD F1,

          BDF_Type,

          [BDF_Depth Structure],

          BDF_Division,

          [ISO Code],

          F6 as country,

          [2017 P02],

          [2017 P03],

          [2017 P04],

          F10,

          F11,

          F12,

          [2017 Q1 MBR IV],

          [2017 Q2 MBR IV],

          [2017 Q3 MBR IV],

          [2017 Q4 MBR IV],

          [2017 MBR IV],

          [2018 MBR IV]

      FROM

      [C:\Users\royce\Desktop\siemens\assignment 1\FY17_BDF .xlsx]

      (ooxml, embedded labels, table is [E2C LC], filters(

      Remove(Col, Pos(Top, 29)),

      Remove(Col, Pos(Top, 21)),

      Remove(Col, Pos(Top, 20)),

      Remove(Col, Pos(Top, 17)),

      Remove(Col, Pos(Top, 16)),

      Remove(Col, Pos(Top, 15)),

      Remove(Col, Pos(Top, 14)),

      Remove(Col, Pos(Top, 13)),

      Remove(Col, Pos(Top, 12)),

      Remove(Col, Pos(Top, 11)),

      Remove(Col, Pos(Top, 10))

      ));

       

      temp2:

      CrossTable(date,cash,6)

      LOAD F1,

          BDF_Type,

          [BDF_Depth Structure],

          BDF_Division, <------ (((( THIS WORKS WHEN IT IS NAMED ANYTHING BUT BDF_DIVISON!!)

          [ISO Code],

          F6 as country,

          [2015 P02],

          [2015 P03],

          [2015 P04],

          [2015 P05],

          [2015 P06],

          [2015 P07],

          [2015 P08],

          [2015 P09],

          [2015 P10],

          [2015 P11],

          [2015 P12],

          F18,

          F19,

          F20,

          F21,

          F22,

          F23,

          [2016 P02],

          [2016 P03],

          [2016 P04],

          [2016 P05],

          [2016 P06],

          [2016 P07],

          [2016 P08],

          [2016 P09],

          [2016 P10],

          [2016 P11],

          [2016 P12],

          F35,

          F36,

          F37,

          F38,

          F39,

          F40,

          [2017 Q1 BDG],

          [2017 Q2 BDG],

          [2017 Q3 BDG],

          [2017 Q4 BDG],

          F45

      FROM

      [C:\Users\royce\Desktop\siemens\assignment 1\FY17_BDF .xlsx]

      (ooxml, embedded labels, table is [E2C LC PY], filters(

      Remove(Col, Pos(Top, 8)),

      Remove(Col, Pos(Top, 7))

      ));

       

      NoConcatenate

      tt1:

      Concatenate(t1)

      load*

      Resident temp2;

       

       

      DROP Table temp2;

       

       

      temp3:

      CrossTable(date,cash,6)

      LOAD F1,

           BDF_Type,

           [BDF_Depth Structure],

           BDF_Division,

           [ISO Code],

           F6 as country,

           [2017 P02],

           [2017 P03],

           [2017 P04],

           F10,

           F11,

           F12,

           [2017 Q1 FC PM],

           [2017 Q2 FC PM],

           [2017 Q3 FC PM],

           [2017 Q4 FC PM],

           F17,

           [2017 Q1 FC],

           [2017 Q2 FC],

           [2017 Q3 FC],

           [2017 Q4 FC]

      FROM

      [C:\Users\royce\Desktop\siemens\assignment 1\FY17_BDF .xlsx]

      (ooxml, embedded labels, table is [MR LC], filters(

      Remove(Col, Pos(Top, 17)),

      Remove(Col, Pos(Top, 16)),

      Remove(Col, Pos(Top, 15)),

      Remove(Col, Pos(Top, 14)),

      Remove(Col, Pos(Top, 13)),

      Remove(Col, Pos(Top, 12)),

      Remove(Col, Pos(Top, 11)),

      Remove(Col, Pos(Top, 10)),

      Remove(Col, Pos(Top, 13)),

      Remove(Col, Pos(Top, 12)),

      Remove(Col, Pos(Top, 25)),

      Remove(Col, Pos(Top, 24)),

      Remove(Col, Pos(Top, 23)),

      Remove(Col, Pos(Top, 22))

      ));

       

       

      NoConcatenate

      tt2:

      Concatenate(t1)

      load *

      Resident temp3;

      drop Table temp3;

       

       

      Thanks guys

        • Re: concatenate issue or bug with qlikview ?
          Anil Babu Samineni

          May be use

           

          UPPER(BDF_Division) as BDF_DIVISION

          • Re: concatenate issue or bug with qlikview ?
            Jonathan Dienst

            Temp2 is being auto-concatenated into t1, so all the references to Temp2 will give errors as the table does not exist. I don't think NoConcatenate and Concatenate can be used in the same load statement. The NoConcatenate  is actually redundant.

             

            Try this load logic:

             

            Load t1

            Concatenate(t1) Load t2

            Concatenate(t1) Load t3

              • Re: concatenate issue or bug with qlikview ?
                Royce Tang

                Dear Jonathan,

                 

                I have tried the logic you proposed however I do not believe I did it the correct way. I wrote the syntax as of such

                 

                SET ThousandSep=',';

                SET DecimalSep='.';

                SET MoneyThousandSep=',';

                SET MoneyDecimalSep='.';

                SET MoneyFormat='$#,##0.00;($#,##0.00)';

                SET TimeFormat='h:mm:ss TT';

                SET DateFormat='M/D/YYYY';

                SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';

                SET FirstWeekDay=6;

                SET BrokenWeeks=1;

                SET ReferenceDay=0;

                SET FirstMonthOfYear=1;

                SET CollationLocale='en-US';

                SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

                SET LongMonthNames='January;February;March;April;May;June;July;August;September;October;November;December';

                SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

                SET LongDayNames='Monday;Tuesday;Wednesday;Thursday;Friday;Saturday;Sunday';

                 

                t1:

                CrossTable(date,cash,6)

                LOAD F1,

                    BDF_Type,

                    [BDF_Depth Structure],

                    BDF_Division,

                    [ISO Code],

                    F6 as country,

                    [2017 P02],

                    [2017 P03],

                    [2017 P04],

                    F10,

                    F11,

                    F12,

                    [2017 Q1 MBR IV],

                    [2017 Q2 MBR IV],

                    [2017 Q3 MBR IV],

                    [2017 Q4 MBR IV],

                    [2017 MBR IV],

                    [2018 MBR IV]

                FROM

                [C:\Users\royce\Desktop\siemens\assignment 1\FY17_BDF .xlsx]

                (ooxml, embedded labels, table is [E2C LC], filters(

                Remove(Col, Pos(Top, 29)),

                Remove(Col, Pos(Top, 21)),

                Remove(Col, Pos(Top, 20)),

                Remove(Col, Pos(Top, 17)),

                Remove(Col, Pos(Top, 16)),

                Remove(Col, Pos(Top, 15)),

                Remove(Col, Pos(Top, 14)),

                Remove(Col, Pos(Top, 13)),

                Remove(Col, Pos(Top, 12)),

                Remove(Col, Pos(Top, 11)),

                Remove(Col, Pos(Top, 10))

                ));

                 

                temp2:

                CrossTable(date,cash,6)

                LOAD F1,

                    BDF_Type,

                    [BDF_Depth Structure],

                    BDF_Division, <------ (((( THIS WORKS WHEN IT IS NAMED ANYTHING BUT BDF_DIVISON!!)

                    [ISO Code],

                    F6 as country,

                    [2015 P02],

                    [2015 P03],

                    [2015 P04],

                    [2015 P05],

                    [2015 P06],

                    [2015 P07],

                    [2015 P08],

                    [2015 P09],

                    [2015 P10],

                    [2015 P11],

                    [2015 P12],

                    F18,

                    F19,

                    F20,

                    F21,

                    F22,

                    F23,

                    [2016 P02],

                    [2016 P03],

                    [2016 P04],

                    [2016 P05],

                    [2016 P06],

                    [2016 P07],

                    [2016 P08],

                    [2016 P09],

                    [2016 P10],

                    [2016 P11],

                    [2016 P12],

                    F35,

                    F36,

                    F37,

                    F38,

                    F39,

                    F40,

                    [2017 Q1 BDG],

                    [2017 Q2 BDG],

                    [2017 Q3 BDG],

                    [2017 Q4 BDG],

                    F45

                FROM

                [C:\Users\royce\Desktop\siemens\assignment 1\FY17_BDF .xlsx]

                (ooxml, embedded labels, table is [E2C LC PY], filters(

                Remove(Col, Pos(Top, 8)),

                Remove(Col, Pos(Top, 7))

                ));

                 

                Concatenate(t1)

                load*

                Resident temp2;

                 

                DROP Table temp2;

                 

                 

                temp3:

                CrossTable(date,cash,6)

                LOAD F1,

                     BDF_Type,

                     [BDF_Depth Structure],

                     BDF_Division,

                     [ISO Code],

                     F6 as country,

                     [2017 P02],

                     [2017 P03],

                     [2017 P04],

                     F10,

                     F11,

                     F12,

                     [2017 Q1 FC PM],

                     [2017 Q2 FC PM],

                     [2017 Q3 FC PM],

                     [2017 Q4 FC PM],

                     F17,

                     [2017 Q1 FC],

                     [2017 Q2 FC],

                     [2017 Q3 FC],

                     [2017 Q4 FC]

                FROM

                [C:\Users\royce\Desktop\siemens\assignment 1\FY17_BDF .xlsx]

                (ooxml, embedded labels, table is [MR LC], filters(

                Remove(Col, Pos(Top, 17)),

                Remove(Col, Pos(Top, 16)),

                Remove(Col, Pos(Top, 15)),

                Remove(Col, Pos(Top, 14)),

                Remove(Col, Pos(Top, 13)),

                Remove(Col, Pos(Top, 12)),

                Remove(Col, Pos(Top, 11)),

                Remove(Col, Pos(Top, 10)),

                Remove(Col, Pos(Top, 13)),

                Remove(Col, Pos(Top, 12)),

                Remove(Col, Pos(Top, 25)),

                Remove(Col, Pos(Top, 24)),

                Remove(Col, Pos(Top, 23)),

                Remove(Col, Pos(Top, 22))

                ));

                 

                Concatenate(t1)

                load *

                Resident temp3;

                drop Table temp3;

                • Re: concatenate issue or bug with qlikview ?
                  Royce Tang

                  Managed to solve it cheers !