select from qvd using macro

[farolito20]

How to do this in vbscript?

SELECT FC as factConv

                                                             FROM vConversionTC.qvd (qvd)

     WHERE ID=2

[farolito20]

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

[farolito20]

REATE FUNCTION [dbo].[fnConvTC]

(

          @TCO nvarchar(3),

          @TCD nvarchar(3),

          @cantBase float,

          @noCia int = 100

)

RETURNS float

AS

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.

[farolito20]

Do you have any idea? 😕

[Anonymous]

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

[farolito20]

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.

[Anonymous]

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

[farolito20]

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

AS

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?

[Anonymous]

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

[farolito20]

Ok

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);