Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
cliff_clayman
Creator II
Creator II

JOIN failing using a Crosstable

I am trying to use the Crosstable function and then Join to another table i previously loaded.  The join does not seem to be working as I am getting duplicate data.  I've attached a sample of the QVW that is not working.  I know I can load them as separate tables and then join to them, but I would like to know why I cannot get this to work using Crosstable.

8 Replies
swuehl
MVP
MVP

I don't know your original data, but the CROSSTABLE LOAD transform your input file that maybe shows a single line per 'Supplier number-BS' to multiple lines (one for each column of your input file).

The Crosstable Load

If you then JOIN this table to another table by key 'Supplier number-BS', your records in this table will be duplicated.

That's how a JOIN works. Maybe you are missing another, additional key field?

cliff_clayman
Creator II
Creator II
Author

If I provided some data, could you take a look at it?

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

In your data, I see each "Supplier number-BS" has a row for each "Business State"  generated by the crosstable,  which is what I would expect.

The "Supplier number-BS"=0 has multiple rows for a state, but I expect that is because table Sheet1 had multiple rows for the "0" value.

-Rob

swuehl
MVP
MVP

Cliff Clayman wrote:

If I provided some data, could you take a look at it?

Sure, that would be helpful.

Provide some sample data (your excel file or some INLINE data tables that show the same effect, the QVW should be reloadable).

And maybe point us to some records where your data gets duplicated and what you expect to see instead given your input files.

swuehl
MVP
MVP

Ok.

And what do you expect to see e.g. for Contact SAP Vendor No.=  0?

Note that this field is not a primary key in your table.

cliff_clayman
Creator II
Creator II
Author

Yes, if the value is 0, then I expect to see 0.

swuehl
MVP
MVP

No, that's not what I meant.

You were mentioning that data gets duplicated in your OP.

As far as I see, that's due to your key field not being a primary key, so you get multiple records in your first table joined with multiple records in your second table (for business states that show a true  StateFlag for multiple lines).

The JOIN will create a resulting table with all combinations of first table records and second table records per key.

Does this sounds reasonable?

Or put it another way: you mentioned that you can get your expected result using a different script.

Could you post this script?

swuehl
MVP
MVP

Are you maybe looking for something like attached?

I've used the record number as additional key for the JOIN, so a StateFlag only joins to the record where it was defined.