Explain the orphan table bursting

[evan_kurowski]

Greetings All,

Can anyone explain to me why the following script/datasource combination loading via a CrossTable() is creating an orphan table for each currency column it attempts to transpose?  I thought that each field not defined as a 'header' in the CrossTable would be dropped automatically, but something here is making that not the case.  Any insight is much appreciated.

SET ThousandSep='.';

SET DecimalSep=',';

SET MoneyThousandSep='.';

SET MoneyDecimalSep=',';

SET MoneyFormat='#.##0,00 ;-#.##0,00 ';

SET TimeFormat='h:mm:ss';

SET DateFormat='DD/MM/YYYY';

SET TimestampFormat='DD/MM/YYYY h:mm:ss[.fff]';

SET MonthNames='ene;feb;mar;abr;may;jun;jul;ago;sep;oct;nov;dic';

SET DayNames='lun;mar;mié;jue;vie;sáb;dom';

MultiCurrency:

CrossTable(ForeignCurrency, CurrencyValue, 3)

FIRST 1

LOAD LocalCurrency,

     DateFrom,

     DateTo,

     EUR,

     USD,

     GBP,

     ARS,

     MXN,

     PLN,

     CZK,

     BRL,

     CLP

FROM

[MultiCurrency.QVD] (qvd);

[evan_kurowski]

Greetings All,

Can anyone explain to me why the following script/datasource combination loading via a CrossTable() is creating an orphan table for each currency column it attempts to transpose?  I thought that each field not defined as a 'header' in the CrossTable would be dropped automatically, but something here is making that not the case.  Any insight is much appreciated.

SET ThousandSep='.';

SET DecimalSep=',';

SET MoneyThousandSep='.';

SET MoneyDecimalSep=',';

SET MoneyFormat='#.##0,00 ;-#.##0,00 ';

SET TimeFormat='h:mm:ss';

SET DateFormat='DD/MM/YYYY';

SET TimestampFormat='DD/MM/YYYY h:mm:ss[.fff]';

SET MonthNames='ene;feb;mar;abr;may;jun;jul;ago;sep;oct;nov;dic';

SET DayNames='lun;mar;mié;jue;vie;sáb;dom';

MultiCurrency:

CrossTable(ForeignCurrency, CurrencyValue, 3)

FIRST 1

LOAD LocalCurrency,

     DateFrom,

     DateTo,

     EUR,

     USD,

     GBP,

     ARS,

     MXN,

     PLN,

     CZK,

     BRL,

     CLP

FROM

[MultiCurrency.QVD] (qvd);

[evan_kurowski]

Greetings All,

Can anyone explain to me why the following script/datasource combination loading via a CrossTable() is creating an orphan table for each currency column it attempts to transpose?  I thought that each field not defined as a 'header' in the CrossTable would be dropped automatically, but something here is making that not the case.  Any insight is much appreciated.

SET ThousandSep='.';

SET DecimalSep=',';

SET MoneyThousandSep='.';

SET MoneyDecimalSep=',';

SET MoneyFormat='#.##0,00 ;-#.##0,00 ';

SET TimeFormat='h:mm:ss';

SET DateFormat='DD/MM/YYYY';

SET TimestampFormat='DD/MM/YYYY h:mm:ss[.fff]';

SET MonthNames='ene;feb;mar;abr;may;jun;jul;ago;sep;oct;nov;dic';

SET DayNames='lun;mar;mié;jue;vie;sáb;dom';

MultiCurrency:

CrossTable(ForeignCurrency, CurrencyValue, 3)

FIRST 1

LOAD LocalCurrency,

     DateFrom,

     DateTo,

     EUR,

     USD,

     GBP,

     ARS,

     MXN,

     PLN,

     CZK,

     BRL,

     CLP

FROM

[MultiCurrency.QVD] (qvd);

[evan_kurowski]

Greetings All,

Can anyone explain to me why the following script/datasource combination loading via a CrossTable() is creating an orphan table for each currency column it attempts to transpose?  I thought that each field not defined as a 'header' in the CrossTable would be dropped automatically, but something here is making that not the case.  Any insight is much appreciated.

SET ThousandSep='.';

SET DecimalSep=',';

SET MoneyThousandSep='.';

SET MoneyDecimalSep=',';

SET MoneyFormat='#.##0,00 ;-#.##0,00 ';

SET TimeFormat='h:mm:ss';

SET DateFormat='DD/MM/YYYY';

SET TimestampFormat='DD/MM/YYYY h:mm:ss[.fff]';

SET MonthNames='ene;feb;mar;abr;may;jun;jul;ago;sep;oct;nov;dic';

SET DayNames='lun;mar;mié;jue;vie;sáb;dom';

MultiCurrency:

CrossTable(ForeignCurrency, CurrencyValue, 3)

FIRST 1

LOAD LocalCurrency,

     DateFrom,

     DateTo,

     EUR,

     USD,

     GBP,

     ARS,

     MXN,

     PLN,

     CZK,

     BRL,

     CLP

FROM

[MultiCurrency.QVD] (qvd);

[#5]

Hii... Evan

yesterday I also faced such problem.... I tried to solve it by using WHERE 1=1, but luckily i got another way to solve such issue.

you just create Intermediate QVD's and Drop all Tables, and when you start development, fetch the data from these Intermediate QVD's in your final document, then you will not face the problem of such Orphan tables.

Hope It will help.

Cheers,

Ahmed.( Looking for a Job)

[#6]

Hii... Evan

yesterday I also faced such problem.... I tried to solve it by using WHERE 1=1, but luckily i got another way to solve such issue.

you just create Intermediate QVD's and Drop all Tables, and when you start development, fetch the data from these Intermediate QVD's in your final document, then you will not face the problem of such Orphan tables.

Hope It will help.

Cheers,

Ahmed.( Looking for a Job)

[evan_kurowski]

Hello Mohd,

Glad you solved your issue, as you can see in Sara's comment, this thread is actually a few years old, but always a good scenario to revisit.

I think part of the original scenario may also have been inhibited from automatically producing one concatenated table because of the combination of the CROSSTABLE keyword with the FIRST 1 syntax.  When the FIRST syntax was removed the syntax resumed creating a single table. 

Perhaps if you took the attached files and demonstrated your approach in conjunction with the attachments, it would be worth a "new" Correct Answer to this thread.

[evan_kurowski]

Hello Mohd,

Glad you solved your issue, as you can see in Sara's comment, this thread is actually a few years old, but always a good scenario to revisit.

I think part of the original scenario may also have been inhibited from automatically producing one concatenated table because of the combination of the CROSSTABLE keyword with the FIRST 1 syntax.  When the FIRST syntax was removed the syntax resumed creating a single table. 

Perhaps if you took the attached files and demonstrated your approach in conjunction with the attachments, it would be worth a "new" Correct Answer to this thread.