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