topic Re: Giving a crosstable a unique ID Key in QlikView

Giving a crosstable a unique ID Key

Thu, 16 May 2013 19:47:04 GMT

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

Re: Giving a crosstable a unique ID Key

rwunderlich — Fri, 17 May 2013 14:45:31 GMT

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

Re: Giving a crosstable a unique ID Key

Fri, 17 May 2013 14:49:46 GMT

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.

Re: Giving a crosstable a unique ID Key

Mon, 20 May 2013 08:33:37 GMT

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.