Giving a crosstable a unique ID Key

[#1]

Hi,

I have a couple of tables that are meant to be related to each other however they currently are not and I believe this is because I dont/cant get the cross table to have an ID key that matched my other table.

Blow is how the tables look in Excel. I want the Group Name and the Performance Date to be the ID key that links the tables.

My code currently reads:

Table2:

LOAD
     date(XX_Performance_Date,'MMM-YY') as Performance_Date,
     year(XX_Performance_Date) as Performance_Year,
     [Group name], 

     [ID: Group&Date]
FROM

(ooxml, embedded labels, table is [Tab3 xxxx details]);

Table1Churn: //the reason I am using LOAD*FROM is so that as new columns are added to the Excel file, the update requires no change in script.
CrossTable(DateTemp, Person_Name, 1)
LOAD * FROM

(ooxml, embedded labels, table is [Tab5 xxx matrix]);


Churn: //this transformation means that the cross table date is transformed from a "string" to a "number" then finally to a date. The date matches the other date fields.
LOAD*,date(num#(DateTemp),'MMM-YY') as Performance_Date
resident Table1Churn;
drop field DateTemp;

I also tried adding in this but it created looping and so for not I have commented in out:

//LOAD
//          Performance_Date,
//          year(Performance_Date) as Performance_Year
//RESIDENT Table1_Churn;

As ever, help is much appreciated!

Isabel

[rwunderlich]

Your original calculation of date is incorrect.

date(XX_Performance_Date,'MMM-YY') as Performance_Date

This only effects the display format, it does not change the internal numeric format used for linking.

Both "01/15/2013" and "01/22/2013" will display as "Jan-13" but they will not link.

The correction is to include a MonthStart function to assign the same day each month.

date(MonthStart(XX_Performance_Date),'MMM-YY') as Performance_Date

-Rob

[#3]

Hi Rob. I am actually only ever using the first date of the month for all the dates that input into this model so this should not be the problem.

[#4]

I found that the solution was to concatinate (&) the fields together an a new table, add in all the fields again here and then re-name the fields so that the link to the original table is broken.