ODBC/Excel join

[danielnevitt]

Hi

Is it possible to join an excel file to an ODBC connection and then create a table (with expression calculations) using the joined data?

I hope that makes sense.

Regards,

Daniel

[lakshmikandh]

Check this Performing a join on data loaded from SQL and Excel

[its_anandrjs]

Yes it is possible can you share an sample script.

Ex:-

Load From <SQL table>

Join

Load From <Any Other Table>

Regards

Anand

[danielnevitt]

Thanks Anand.

Is it possible to then create a table in the load statement using this joined data?  If so, are you able to advise how I would do this?

Regards,

Daniel

[its_anandrjs]

Ex:-

TableSQL:

ODBC CONNECT

SQL SELECT

From SQL File

JOIN(TableSQL)

Load * From Location;

Regards,

Anand

[danielnevitt]

Hi Anand,

Sorry I am really struggling with this one.

I have the below code which is joined to an ODBC sql (large code) on udf_code.  I have a field in the sql named unpriced_lots that is used in the new table.

Table:

left join LOAD [Contract Name],
[Rule Chapter],
[Commodity Code] as udf_code,
[Contract Size],
[Contract Units],
Type,
Settlement,
Group,
[Diminishing Balance Contract],
[Reporting Level],
[Spot-Month position comprised of futures and deliveries],
[Spot-Month Aggregate Into Futures Equivalent Leg (1)],
[Spot-Month Aggregate Into Futures Equivalent Leg (2)],
[Spot-Month Aggregate Into Ratio Leg (1)],
[Ratio Leg (1)],
[Spot-Month Aggregate Into Ratio Leg (2)],
[Ratio Leg (2)],
[Spot-Month Accountability Level],
[Daily Accountability Level (For Daily Contract)],
[Initial Spot-Month Limit (In Net Futures Equivalents) Leg (1) / Leg (2)],
[Initial Spot-Month Limit Effective Date],
[Spot-Month Limit (In Contract Units) Leg (1) / Leg (2)],
[Second Spot-Month Limit (In Net Futures Equivalents)],
[Second Spot-Month Limit Effective Date],
[Single Month Aggregate Into Futures Equivalent Leg (1)],
[Single Month Aggregate Into Futures Equivalent Leg (2)],
[Single Month Aggregate Into Ratio Leg (1)],
[Single Month Aggregate Into Ratio Leg (2)],
[Single Month Accountability Level Leg (1) / Leg (2)],
[Single Month Limit (In Net Futures Equivalents) Leg (1) / Leg (2)],
[All Month Aggregate Into Futures Equivalent Leg (1)],
[All Month Aggregate Into Futures Equivalent Leg (2)],
[All Month Aggregate Into Ratio Leg (1)],
[All Month Aggregate Into Ratio Leg (2)],
[All Month Accountability Level Leg (1) / Leg (2)],
[All Month Limit (In Net Futures Equivalents) Leg (1) / Leg (2)]

FROM

(ooxml, embedded labels, header is 1 lines, table is [Ch 5 Table]);

FinalTable:

LOAD exchange_cd,
[Commodity Code] as udf_code,
formatdate,
Sum(unpriced_lots) as SumUnpricedLots,
[Spot-Month Aggregate Into Ratio Leg (1)],
Sum(unpriced_lots*[Ratio Leg (1)]) as TotalLeg1,
[Spot-Month Aggregate Into Ratio Leg (2)],
Sum(unpriced_lots*[Ratio Leg (2)]) as TotalLeg1

Resident Table;

DROP Table Table, FinalTable; 

However I get the following error message.  Do you have any idea what I am doing wrong?

Regards,

Daniel

[its_anandrjs]

Hi,

If you use

Ex:-

Resident Table;

Then your table is not identified because it is left join to the table you have to give the main table name

Ex:-

Maintable:

Load * From Lcoation;

Left Join

Load * from AnyTable;

Load * Resident MainTable;

Drop Table MainTable;

In Your Example:-

Table:

Laod * From Source;

left join (Table)

LOAD [Contract Name],
[Rule Chapter],
[Commodity Code] as udf_code,
[Contract Size],
[Contract Units],
Type,
Settlement,
Group,
[Diminishing Balance Contract],
[Reporting Level],
[Spot-Month position comprised of futures and deliveries],
[Spot-Month Aggregate Into Futures Equivalent Leg (1)],
[Spot-Month Aggregate Into Futures Equivalent Leg (2)],
[Spot-Month Aggregate Into Ratio Leg (1)],
[Ratio Leg (1)],
[Spot-Month Aggregate Into Ratio Leg (2)],
[Ratio Leg (2)],
[Spot-Month Accountability Level],
[Daily Accountability Level (For Daily Contract)],
[Initial Spot-Month Limit (In Net Futures Equivalents) Leg (1) / Leg (2)],
[Initial Spot-Month Limit Effective Date],
[Spot-Month Limit (In Contract Units) Leg (1) / Leg (2)],
[Second Spot-Month Limit (In Net Futures Equivalents)],
[Second Spot-Month Limit Effective Date],
[Single Month Aggregate Into Futures Equivalent Leg (1)],
[Single Month Aggregate Into Futures Equivalent Leg (2)],
[Single Month Aggregate Into Ratio Leg (1)],
[Single Month Aggregate Into Ratio Leg (2)],
[Single Month Accountability Level Leg (1) / Leg (2)],
[Single Month Limit (In Net Futures Equivalents) Leg (1) / Leg (2)],
[All Month Aggregate Into Futures Equivalent Leg (1)],
[All Month Aggregate Into Futures Equivalent Leg (2)],
[All Month Aggregate Into Ratio Leg (1)],
[All Month Aggregate Into Ratio Leg (2)],
[All Month Accountability Level Leg (1) / Leg (2)],
[All Month Limit (In Net Futures Equivalents) Leg (1) / Leg (2)]
FROM

(ooxml, embedded labels, header is 1 lines, table is [Ch 5 Table]);

FinalTable:
LOAD exchange_cd,
[Commodity Code] as udf_code,
formatdate,
Sum(unpriced_lots) as SumUnpricedLots,
[Spot-Month Aggregate Into Ratio Leg (1)],
Sum(unpriced_lots*[Ratio Leg (1)]) as TotalLeg1,
[Spot-Month Aggregate Into Ratio Leg (2)],
Sum(unpriced_lots*[Ratio Leg (2)]) as TotalLeg1
Resident Table;

DROP Table Table; 

Regards

Anand