Creating join key for power pivot

[jpl458]

I have weather data and financial data nad need to create a join key for each. The weather data give me a date time (Grenwich mean time) in the format YYYYMMDDHRMM, and the financial data hals a locael time doen to the second. I can create a Unique key for the weather data because the data is collected hourly, si if I can create a key that has yyyymmddhh, (leaving off the seconds I'm in business. I local time from the weather time by subtracting 6 hours from the zulu time, and do that by extracting each part from the zulutime and calculate the Fiscal year month etc. The problem is the the month, day and hour can have single digits (1 instead of 01). This creates duplicate in the weather key.
How can I create mm dd hh so that they have two character e.g 01 02 03 etc. In access the format function does it great, leaves the leading zero, but the format function in Excel is not the same.

Any help would be appreciated.

Thanks in advance

jpl

 

[SkipVought]

hi,

What application?

Where is your SQL?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

...and what database? Access, Oracle, SQL Server, BD2?????

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[jpl458]

The weather dabase is in ACCESS the financial data in a Progress DB, both of which I can get to via ODBC. I want to get both of those data in EXCEL Powerpivot, and get the financial data directly, not through ACCESS, which I can do. But I need to build a join key as explained above. I guess it comes down to - how do I change a number 5 to a 05 in EXCEL (two places, not one). Then I can build the join Key in EXCEl PowerPivot. Very easy in ACCES but not in EXCEL. I can create the keys if I get both sets of data via ACCESS, and use the Format Function in there to get the YYYYMMDDHH, which I can conatenate in the same query that gets the data, then get both tables Via PowerPivot and do the join in there. Want to do this not using ACCESS, which is an extra step.

Hope this is clear enough.

Thanks

jpl

 

[SkipVought]

Hi,

If I understand your requirements, you need to join tables from two different databases.

That cannot be done directly in MS Query.

However, it seems that you already have BOTH these DBS linked in an Access db. So, you could query these tables from Excel MS Query, thru The Access db.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}