topic Join Excel Column with SQL query for operations in QlikView

Join Excel Column with SQL query for operations

Wed, 28 Mar 2012 12:07:46 GMT

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?

Join Excel Column with SQL query for operations

CELAMBARASAN — Wed, 28 Mar 2012 12:11:38 GMT

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

Join Excel Column with SQL query for operations

Wed, 28 Mar 2012 14:32:35 GMT

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[ ] ?

Join Excel Column with SQL query for operations

CELAMBARASAN — Wed, 28 Mar 2012 14:40:03 GMT

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

Join Excel Column with SQL query for operations

sebastiandperei — Wed, 28 Mar 2012 15:34:28 GMT

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...

Re: Join Excel Column with SQL query for operations

Fri, 30 Mar 2012 18:10:35 GMT

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;

Join Excel Column with SQL query for operations

sebastiandperei — Fri, 30 Mar 2012 20:02:19 GMT

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;

Join Excel Column with SQL query for operations

Fri, 30 Mar 2012 23:08:26 GMT

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

Re: Join Excel Column with SQL query for operations

Tue, 03 Apr 2012 14:43:17 GMT

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!!!