topic Re: select from qvd using macro in QlikView

select from qvd using macro

farolito20 — Fri, 11 Jan 2013 16:40:18 GMT

How to do this in vbscript?

SELECT FC as factConv

FROM vConversionTC.qvd (qvd)

WHERE ID=2

Re: select from qvd using macro

farolito20 — Fri, 11 Jan 2013 16:42:44 GMT

I have this sub..end sub, but I need to use it in LOAD statement but's not possible. So i need move it to a vbscript.

How to do that?

SUB fnConvTC(TCO1,TCD1,cantBase)

LOAD FC as factConv FROM vConversionTC.qvd (qvd) WHERE TCO=TCO1 AND TCD=TCD1;

LET cantReturnTC = IF (TCO1=TCD1,cantBase,cantBase*factConv);

END SUB

Re: select from qvd using macro

farolito20 — Fri, 11 Jan 2013 18:00:01 GMT

REATE FUNCTION [dbo].[fnConvTC]

(

@TCO nvarchar(3),

@TCD nvarchar(3),

@cantBase float,

@noCia int = 100

)

RETURNS float

BEGIN

DECLARE @cantReturn float, @factConv float

IF(@TCO = @TCD)

BEGIN

SET @cantReturn = @cantBase

END

ELSE

BEGIN

SET @factConv = (SELECT FC as factConv

FROM vConversionTC

WHERE TCO = @TCO AND TCD=@TCD

AND codCompania = @noCia)

SET @cantReturn = @cantBase * @factConv

END

RETURN (@cantReturn)

END

That's my function in sql. I wirte this in macro.

I have the table vConversionTC in qvd.

Re: select from qvd using macro

farolito20 — Fri, 11 Jan 2013 18:09:39 GMT

Do you have any idea? 😕

Re: select from qvd using macro

Anonymous — Fri, 11 Jan 2013 18:56:02 GMT

See this script:

LET vTCO=<some value>;
LET vTCD=<some value>;
LET noCia=100;
LET cantBase=<some value>;

table:

LOAD
FC as factConv,
FC * $(cantBase) as cantReturn
FROM vConversionTC.qvd (qvd)
WHERE TCO= $(vTCO)
and TCD=$(vTCD)
and codCompania = $(noCia);

Regards,
Michael

Re: select from qvd using macro

farolito20 — Fri, 11 Jan 2013 19:51:55 GMT

I need this in macro :s

LOAD t_orno,

t_bprn,

t_opes,

fnConvTC(C.t_ccur, 'USD', A.t_prif, 100) AS mtoFDO,

FROM ttdcse401100.qvd

That's like what I need.

I need a function in qlikview which recieve 4 parameters and return a value.

I can't use a souboutine beacuse it's impossible call it in LOAD, so I need a function in a macro.

Re: select from qvd using macro

Anonymous — Fri, 11 Jan 2013 20:25:10 GMT

Sorry, I don't understand.

Normally you define what you want to do, not how you are going to do this. Getting data from QVD or any other data source is done through the script. You can create macro functions that can be used in the script, but it dosn't look that you need this either...

Re: select from qvd using macro

farolito20 — Fri, 11 Jan 2013 20:30:22 GMT

Ok, forget the macro script.

Help me please.

I have this function

REATE FUNCTION [dbo].[fnConvTC]

(

@TCO nvarchar(3),

@TCD nvarchar(3),

@cantBase float,

@noCia int = 100

)

RETURNS float

BEGIN

DECLARE @cantReturn float, @factConv float

IF(@TCO = @TCD)

BEGIN

SET @cantReturn = @cantBase

END

ELSE

BEGIN

SET @factConv = (SELECT FC as factConv

FROM vConversionTC

WHERE TCO = @TCO AND TCD=@TCD

AND codCompania = @noCia)

SET @cantReturn = @cantBase * @factConv

END

RETURN (@cantReturn)

END

-----------------------------------------------------------------------------------------------------------------------------------------

I need extract one value from sql and load it in qlikview, but using the function fnConvTC, to load that value but converted (with this function).

How?

Re: select from qvd using macro

Anonymous — Fri, 11 Jan 2013 20:53:55 GMT

Well, in this case see my first response at 1:56 PM

Re: select from qvd using macro

farolito20 — Fri, 11 Jan 2013 21:05:37 GMT

This is my load

table1:

LOAD TCO,

TCD,

cantBase,

FROM table;

Then your code, but How assign value to that variables?

LET vTCO=<some value>;
LET vTCD=<some value>;
LET noCia=100;
LET cantBase=<some value>;

table2:

LOAD
FC as factConv,
FC * $(cantBase) as cantReturn
FROM vConversionTC.qvd (qvd)
WHERE TCO= $(vTCO)
and TCD=$(vTCD)
and codCompania = $(noCia);

Re: select from qvd using macro

farolito20 — Fri, 11 Jan 2013 21:09:26 GMT

and how join both table?

I need this structure:

TCO, TCD, cantBase, cantReturn

Re: select from qvd using macro

Anonymous — Fri, 11 Jan 2013 21:41:18 GMT

Getting better... 🙂

So, you have three fields: TCO, TCD, cantBase
Need to create one more, cantReturn.

Correct?

If yes, we need to know the rules, how to define the new field. My guess, it is based on data in qvd. And qvd has fields: TCO, TCD, FC.

If yes, you can join:

table1:

LOAD TCO,

TCD,

cantBase,

FROM table;

LEFT JOIN (table1) LOAD

TCO,

TCD,

FROM vConversionTC.qvd (qvd);

And after that:

Result:

LOAD

TCO,

TCD,

cantBase,

FC*cantBase as cantReturn

FROM table1;

DROP TABLE table1;

Very possible I've missed some of your rules. Hope you got the idea and can proceed from here.

Regards,

Michael

Re: select from qvd using macro

farolito20 — Fri, 11 Jan 2013 22:18:32 GMT

Another case, I have this load

LOAD codCompania,

codORadiales,

codAlmacen,

t_odat,

codArticulo,

FROM table1;

LOAD codCosecha,

t_fdat,

t_edat,

t_gpci;

FROM table2;

Well, I need codCosecha from table2 when t_gpci=1 and t_odat between t_fdat and t_edat.

Final Table:

load

codCompania,

codORadiales,

codAlmacen,

t_odat,

codArticulo,

codCosecha

Re: select from qvd using macro

Anonymous — Mon, 14 Jan 2013 16:40:41 GMT

I'm not sure I'll have time today. For now, two advices:
1. Specific: See INTERVALMATCH (help and community)
2. General: For a new question, it is always better to start new discussion. If most participants are like me, they seldom read discussions with many replies.

Regards,

Michael