topic Explain the orphan table bursting in QlikView

Explain the orphan table bursting

evan_kurowski — Tue, 15 Apr 2014 19:09:35 GMT

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);

Re: Explain the orphan table bursting

Anonymous — Tue, 15 Apr 2014 19:12:14 GMT

Hello Qlik Community Members- this discussion has been answered previously but accidentally deleted we are recreating the thread. Here are the responses:

Correct Answerby Oleg Troyansky on May 31, 2012 6:11 PM

I can't explain the exact reasons, but it has to do with the optimized load of the QVD file. Apparently, Crosstable doesn't work well with the optimized load.

2 ways to go around the problem:

1. Force the load into a non-optimized load. It's enough to add "WHERE 1=1" at the end to kick it into a non-optimized load. The issue seems to go away.

2. Load * (no crosstable) from QVD into a temporary table and then reload in memory with the Crosstable settings. Seems to work as well.

cheers,

Oleg

674 Views
Tags :
Reply
Correct Answer Explain the orphan table bursting

---------------------------------------------------

EvanKurowski Jun 1, 2012 11:13 AM (in response to Oleg Troyansky)

Thank you Oleg, I added the 'WHERE 1 = 1' and the orphan tables were gone.

Heh, I've been freaking out thinking there was something sneaky in the data, but that makes sense, the optimized load not compatible with Crosstable(). Regular load worked just fine.

---------------------------------------------------

Kalyan Sundaram Oct 24, 2012 12:59 AM (in response to EvanKurowski)

hi evan,

can u pls send the file that was cleared with orphan fields

i cant understand the explaination. pls send the file

Helpful Answer

---------------------------------------------------

Chadd McNicholas Nov 18, 2013 1:21 PM (in response to Oleg Troyansky)

Oh how I love searching the community for answers! I just ran into this problem this morning, and I'm so glad I found this answer. I just tried "WHERE 1", which also did the trick.

Helpful Answer

Re: Explain the orphan table bursting

Tue, 19 Aug 2014 05:29:30 GMT

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)

Re: Explain the orphan table bursting

evan_kurowski — Thu, 21 Aug 2014 20:50:41 GMT

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.