Join Excel Column with SQL query for operations

[#1]

Hi there!

I want to join an Excel table with a SQL query and do some operations to put in a Dynamic Table. What can I do to achieve this? My Excel table is:

TbLucro:

LOAD CODCENCUS,
CODPROJ,
Lucro FROM

(ooxml, embedded labels, table is Plan1);

// And I want to use the Column Lucro to do the following operation:

TbCalculo:

Load LucroResult;

SQL select (a.value1*a.value2)*Lucro as LucroResult from values_table a;

LEFT JOIN (TbLucro)

LOAD *

RESIDENT TbCalculo;

Drop Table TbCalculo;

This is not running, I receiving error: Column Lucro is not recognized. Someone can help me?

[CELAMBARASAN]

Hi,

TbCalculo:

Load LucroResult;

SQL select (a.value1*a.value2)*Lucro as LucroResult from values_table a;

     Change this SQl query to

TbCalculo:

Load Lucro;

SQL select (a.value1*a.value2)*Lucro as LucroResult,Lucro from values_table a;

Celambarasan

[#3]

Error again...Lucro is not recognized...

How can I use this column inside a SELECT clause? The column  "Lucro" must be inside Parentesis ( ) or Brakets[ ] ?

[CELAMBARASAN]

Hi,

     Use this code as it is and check.

TbLucro:

LOAD CODCENCUS,
CODPROJ,
Lucro FROM

(ooxml, embedded labels, table is Plan1);

// And I want to use the Column Lucro to do the following operation:

TbCalculo:

Load LucroResult,Lucro;

SQL select (a.value1*a.value2)*Lucro as LucroResult,Lucro from values_table a;

LEFT JOIN (TbLucro)

LOAD *

RESIDENT TbCalculo;

Drop Table TbCalculo;

Celambarasan

[sebastiandperei]

Hi Pereira!!  we could brothers, and so i will try to help my brother!!!

TbLucro:

(same you did)

TbCalculo:

SQL Select

     Value1*Value2 as Values

From values_table;

Left Join (TbLucro)

Load

     Values*Lucro as LucroResult,

     Lucro,

Resident TbCalculo;

Take in mind that this will cross all pair possible of Value1 and Value 2, with all values of Lucro...

[#6]

Hi people, I'm still receiving error:

Campo não encontrado - <LUCRO>
LEFT JOIN (TbLucro)
LOAD Valor_Desdob_RatTX*LUCRO as RESULTLUCRO, LUCRO
Resident TbCalculo,

Take a look at real code and tell me what's wrong:

TbLucro:
LOAD CODCENCUS,
CODPROJ,
TAXADMIN,
LUCRO
FROM

(ooxml, embedded labels, table is Plan1);

TbCalculo:
LOAD Valor_Desdob_RatTXL;
SQL SELECT (((VRAT.VLRDESDOB)* FIN.RECDESP * 0.15) * -1) as Valor_Desdob_RatTXL
FROM TGFFIN FIN
INNER JOIN VGFFINRAT VRAT ON VRAT.NUFIN = FIN.NUFIN
INNER JOIN TGFNAT NAT ON NAT.CODNAT = VRAT.CODNAT
INNER JOIN TCSPRJ PRJ ON PRJ.CODPROJ = VRAT.CODPROJ
INNER JOIN TSICUS CCU ON CCU.CODCENCUS = VRAT.CODCENCUS
WHERE VRAT.AD_DTCOMP IS NOT NULL AND ((VRAT.VLRDESDOB)* FIN.RECDESP > 0)
AND VRAT.CODNAT IN (1010503, 1010502, 1010501, 1010301, 1010201);

LEFT JOIN (Tblucro)
LOAD Valor_Desdob_RatTXL*LUCRO as RESULTLUCRO, LUCRO
RESIDENT TbCalculo;

[sebastiandperei]

Sorry, but i was mistake.(En español???)

For make a Join, you need at least one common field between both tables.

Think: You will multiply every Valor_Desdob_RatTXL value with every Lucro value, without any aggrupation. Is this what you need?If you want to do this, try:

TbLucro:

(same you did)

TbCalculo:

SQL Select

     Value1*Value2 as Valor_Desdob_RatTXL

From values_table;

Left Join (TbLucro)

Load Valor_Desdob_RatTXL

From TbCalculo;

Left Join (TbLucro)

Load

     Valor_Desdob_RatTXL *Lucro as LucroResult,

     Lucro,

Resident TbLucro;

But, you have some common fields between de TbLucro and the SQL Select: CODPROJ and CODENCUS. If you want to link both tables with field, you must load these fields in TbCalculo too, and then:

Left Join (TbLucro)

Load

     CODPROJ,

     CODENCUS,

     Valor_Desdob_RatTXL

Resident TbCalculo;

Left Join (TbLucro)

Load

     Valor_Desdob_RatTXL,

     Lucro,

     Valor_Desdob_RatTXL*Lucro as RESULTLUCRO

Resident TbLucro;

[#8]

Do you have a field in your excel file named Lucro and another in your SQL table?

It seems that one of them is not recognized.

Maybe you can debug it and check in which instruction is thrown

[#9 · Accepted solution]

Hi All,I'd solved my problem by using Variable, first I'd loaded the SQL Table, after Load Excel File a Set the desired cols as variables...

Aux:
Load
Lucro,
TxAdmin
Resident TbLucro;
Let VarLucro=Peek('Lucro');
Let VarTaxa=Peek('TxAdmin');
Drop Table Aux;

Voilá!!! It's running!!!Thanks for all responses!!!