topic Re: concatenate issue or bug with qlikview ? in QlikView

concatenate issue or bug with qlikview ?

Mon, 20 Feb 2017 10:49:44 GMT

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

(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

(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

(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 — Mon, 20 Feb 2017 11:40:29 GMT

May be use

UPPER(BDF_Division) as BDF_DIVISION

Re: concatenate issue or bug with qlikview ?

Mon, 20 Feb 2017 11:51:14 GMT

yeahs changing it to any other field name works, however I would like to concatenate the data hence I have to make it the same

Re: concatenate issue or bug with qlikview ?

Anil_Babu_Samineni — Mon, 20 Feb 2017 11:54:48 GMT

So then?

Re: concatenate issue or bug with qlikview ?

Mon, 20 Feb 2017 11:57:55 GMT

so using BDF_DIVISION wont achieve the result I want

Re: concatenate issue or bug with qlikview ?

jonathandienst — Mon, 20 Feb 2017 12:01:14 GMT

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 ?

jonathandienst — Mon, 20 Feb 2017 12:03:20 GMT

>> however I would like to concatenate the data hence I have to make it the same

The field must match the source, so you cannot just flip flop the name. The desired name causes the auto-concatenation, but what us the case of the source?

Re: concatenate issue or bug with qlikview ?

Mon, 20 Feb 2017 12:12:07 GMT

Cheers Jonathan! Would the load logic be placed at the bottom of each table ? I will definitely try it out

Re: concatenate issue or bug with qlikview ?

jonathandienst — Mon, 20 Feb 2017 13:05:07 GMT

The load logic is a simple pseudo code for the actual process. You need to flesh out the details with your actual load statements.

Re: concatenate issue or bug with qlikview ?

Mon, 20 Feb 2017 13:38:39 GMT

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

(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

(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

(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 ?

Mon, 20 Feb 2017 16:14:14 GMT

Managed to solve it cheers !