Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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);
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:
cheers,
Oleg
---------------------------------------------------
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
---------------------------------------------------
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.
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)
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.