Crosstable --> $orphan

[#1]

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

[#2]

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 ...

[#3]

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

[bradshields · Accepted solution]

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

[erichshiino]

Thanks Brad, I had the same problem and your suggestion solved it!

[pablolabbe]

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

[sebastiandperei]

WOWWWWWW This works!, it is incredible. Congratulations Brad, your suggest works very good!!!

[iwqa2812]

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

[#9]

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

[Bruno_Calver]

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