topic Help for Use 2 Fields in LOAD! in QlikView

Help for Use 2 Fields in LOAD!

Wed, 16 May 2012 11:48:32 GMT

I want to Use two fields from two tables in one load/resident script for calculate 'PuWt_Status' field.

please help me in Attached Script.

Help for Use 2 Fields in LOAD!

chematos — Wed, 16 May 2012 20:52:57 GMT

Try this:

If(sum(AMOUNT) = round(sum(if(SlipType = 4,total))*0.05) , 'OK' , 'NOK') AS PuWt_Status,

Re: Help for Use 2 Fields in LOAD!

Thu, 17 May 2012 04:02:20 GMT

José Mª Tos wrote:
Try this:
If(sum(AMOUNT) = round(sum(if(SlipType = 4,total))*0.05) , 'OK' , 'NOK') AS PuWt_Status,

TNX for Code

But Not Work Bucause Show Error: <AMOUNT> Field Not Found!

My problem is:

Load <Amount> feild(from Table2) in one LOAD script with <SlipType> Field(from Table1) .

Please Read Attached File OR This CODE:

//==========================SELECT==========================

test:

sql select

slipnr,

docode,

total,

servicecode,

dateslip,

servicename,

bostatus,

SlipType

from "UOD".dbo."V_140_mmtrans" where servicecode in ('NP1001','SS3991','ZS1111') and bostatus != 3;

test2:

sql select TRANSNR,

Status,

LineExp,

DOCODE as docode,

shamsidate,

MiladiDate As dateslip,

AMOUNT,

ARPDesc

from V_140_ARPTrans where transnr like 'WT%' and ArpCode = 'S.IRN.0043';

sql select Shamsi_Date as LastUpdateDate,

Miladi_Date as convMiladi,

convert(varchar,getdate(),108) as LastUpdateTime

from V_S_ShamsiDate where Miladi_Date = convert(varchar,getdate(),102);

//===========================LOAD===========================

LOAD docode,

sum(if(SlipType = 9,total)) As Sales_invoice,

sum(if(SlipType = 4,total)) As Purchase_invoice,

//PLEASE HELP FOR CaCULATE THIS LINE:

If(sum(AMOUNT) = round(sum(if(SlipType = 4,total))*0.05) , 'OK' , 'NOK') AS PuWt_Status,

if(Round(sum(if(SlipType = 9,total))/2)= sum(if(SlipType = 4,total)),'OK','NOK') As SaPu_Status

resident test

group by docode;

//====================================================================

Message was edited by: ramin006

Help for Use 2 Fields in LOAD!

Jason_Michaelides — Thu, 17 May 2012 07:51:39 GMT

You could use a Lookup() function but it would be very slow. More of a concern is your data model, however, and this needs sorting before you can effectively solve this. Is it intentional to have two common fields between the two tables (docode and dateslip)?

Help for Use 2 Fields in LOAD!

Fri, 18 May 2012 19:47:59 GMT

it is intentional to have two common fields between the two tables

I think must create new view in sql server with two tables and use new view in my script!

But i know this solution is not good