Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi
I want to create a crosstable, I've done it but when I take a look with the table viewer every field looks like a separate table with the &orphan and the name of the table as a prefix.
This's how I've done it:
INVENTORY_FAILURE2:
QUALIFY *;
UNQUALIFY CO_PRODUCT;
CROSSTABLE(Failure, Veces, 7)
LOAD co_enterprise,
co_art AS CO_PRODUCT,
DATE,
date(DATE) as Fecha_Inv,
year(date(DATE)) as Anno_Inv,
month(date(DATE)) as Mes_Inv,
day(date(DATE)) as Dia_Inv,
FAILURE1,
FAILURE2,
FAILURE3,
FAILURE4,
FAILURE5
FROM QvD\INVENTORY_FAILURE.QVD (qvd);
What am I doing wrong???
Thxs in advance
I had the same problem. I suspect it is a similar problem to trying to use a qvd in a mapping load. The easiest way to fix is to add something that stops the load of the qvd being optimized. I added "Where 1=1;" to the script of the crosstable load and it removed all the $orphan fields.
Cheers Brad
I think cross tab wizard in broken in 9.00.7440
I can not get it to work with even a very simple example. ( Spreadsheet attached.)
Assuming you want everything in the file I suggest you do it as follows:
CROSSTABLE(Failure, Veces, 7)
Load *
From ...
Well, I didn't notice that the crossed table it was created, but still the problem with the &orphan.
Besides, I've a problem with the same table, when I qualify it, the name of the table it's written twice. Why that happens???
Thxs
I had the same problem. I suspect it is a similar problem to trying to use a qvd in a mapping load. The easiest way to fix is to add something that stops the load of the qvd being optimized. I added "Where 1=1;" to the script of the crosstable load and it removed all the $orphan fields.
Cheers Brad
Thanks Brad, I had the same problem and your suggestion solved it!
Hi Guys,
I found the same behavior using version 11 SR1 and applied the same workaround to disable optimized load to correct this issue.
Thanks,
Pablo Labbe Ibaceta
WOWWWWWW This works!, it is incredible. Congratulations Brad, your suggest works very good!!!
You're a genius Brad. It took me 2 days to work out why all those orphan fields were appearing, the preview of the actual crosstable looked correct. Couldn't figure out what I was doing wrong.
Cheers,
Ivana
Hi Brad
I couldn't get your solution to work - no idea why - but I found a less elegant solution I thought I'd share in case anyone else has the same problem.
QUALIFY *;
a:
LOAD * FROM xyz.xls....;
UNQUALIFY *;
b:
CrossTable(Fieldname, Data, column ref)
LOAD * Resident a;
Drop table a;
I didn't get any orphans
Took me a while to come up with this though
Kathryn
Hi Kathryn,
Great solution. The reason Brad's did not work in this case is all to do with the Qualify statement as far as I can tell. Because the field names in the source data are different to the qualified names I think the CrossTable function gets in a twist and then creates the orphans. Anyway this worked great
Bruno