Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I apologize for the vague header...
I am trying to create a 'Unique Key' table which will be made up of 2 fields. The two fields I am joining are [Negotiator] and [Full Date] (full date is Month and Year spanning May 2015 to December 2017). In short, I want to create a table with every permutation of name and date. I need the unique key because the relationship between name and date becomes many-to-many as I pull in more data points in - example of what I need:
' John May 2015
John June 2015...
... John December 2017
....
Mike May 2015
Mike June 2015...
... Mike December 2017
....
Jane May 2015
Jane June 2015...
.... Jane December 2017 '
I tried keeping it simple and just combine the two, something like: Negotiator & [Full Date] as [Unique Key]
... but quickly realized QV cannot recognize the field [Negotiator] as a relatable field; something to do with it being created in the crosstable.
Below is an outline of my query:
CrossTable([Negotiator], Data_QA)
LOAD Date as [Full Date],
[John Doe],
[Mike Doe],
[Jane Doe],
- (there are about 40 names) -
Hope this is enough information to understand the issue. Any guidance / suggestions on how to create this joined unique key field?
Hi Schyler,
I looked at the sample data and here's what I would do:
t_QA:
crosstable ("Full Date", Data_QA, 1) load *
FROM [lib://Downloads/Sample Data.xlsx]
(ooxml, embedded labels, table is QA);
QA:
noconcatenate load
Negotiator &''& "Full Date" as %Link_key,
Data_QA
resident t_QA;
LinkTable:
noconcatenate load distinct
Negotiator &''& "Full Date" as %Link_key,
Negotiator,
"Full Date"
resident t_QA;
drop table t_QA ;
You should get something like this:
Do the same for the "MA_Table", but use Concatenate or Join to add the records from MA table to the LinkTable.
concatenate (LinkTable) load distinct
Negotiator &''& "Full Date" as %Link_key,
Negotiator,
"Full Date"
resident t_MA;
This is basically how to construct a LinkTable data structure. But, I'm not that sure if you really need it. I think that you could just concatenate the QA and MA tables (after you did the intitial CrossTable load) into one fact table.
Anyway, hope this helps.
Radovan