topic Re: Joining Cross Tables in QlikView

Joining Cross Tables

Tue, 29 Apr 2014 16:41:12 GMT

I am trying to LEFT JOIN a cross table to an existing Cross Table. They share many columns in common, but one column is different in the second table and the second table does not have one of the columns that the first does. When I reload the data I get an error message saying "Illegal Combination of Prefixes". Thoughts? Below is the code:

CrossTable([Functional System], Mileage)

LEFT JOIN ([Summary of Persons Fatally injured in motor vehicle crashes by Functional System])

LOAD YEAR,

     F2 as Interstate_Rural,

     OTHER as [Other Freeways and Expressways Rural],

     OTHER1 as Other_Principal_Arterial_Rural,

     MINOR as Minor_Arterial_Rural,

     MAJOR as Major_Collector_Rural,

     MINOR1 as Minor_Collector_Rural,

     F8 as Local_Rural,

     F9 as Interstate_Urban,

     FREEWAYS as [Other Freeways and Expressways Urban],

     OTHER2 as Other_Principal_Arterial_Urban,

     MINOR2 as Minor_Arterial_Urban,

     MAJOR1 as Major_Collector_Urban,

     MINOR3 as Minor_Collector_Urban,

     F15 as Local_Urban

FROM

(biff, embedded labels, table is SUMMARY$, filters(

Re: Joining Cross Tables

Gysbert_Wassenaar — Tue, 29 Apr 2014 16:52:30 GMT

Switch the first two lines:

Left join (......)

CrossTable(....etc

Load ...etc

Re: Joining Cross Tables

Tue, 29 Apr 2014 17:00:28 GMT

Got the same error message.

Re: Re: Joining Cross Tables

Gysbert_Wassenaar — Tue, 29 Apr 2014 17:15:31 GMT

I'm afraid you'll need a resident load or store one of the tables into a qvd first.

T1:
CrossTable(X,Y)
Load * from somewhere;

T2:
CrossTable(P,Q)
Load * from somewhereelse;
//qvd alternative
//Store T2 into T2.qvd (qvd);
//Drop table T2;

Result:
Left join(T1)
Load * resident T2;
//qvd alternative
// Load * from T2.qvd (qvd);


Drop table T2;

Re: Joining Cross Tables

Tue, 29 Apr 2014 17:16:55 GMT

The last suggestion gave me the same error. The key words do not change to blue when placed at the end of the list of fields.

Re: Re: Joining Cross Tables

Wed, 30 Apr 2014 15:17:48 GMT

Thanks Gysbert. Saving it as a QVD worked...although I am not quite sure why. Could you explain why this worked?

Re: Re: Joining Cross Tables

Wed, 30 Apr 2014 15:21:54 GMT

Never mind. The problem I was having was that I forgot the DROP statement. once I added it, it worked both ways. I understand that what I did was simply combine both tables into one and dropping the table (since I no longer need it) prevents the Synthetic key. Thanks!!