Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Help for Use 2 Fields in LOAD!

Hi

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.

4 Replies
chematos
Specialist II
Specialist II

Try this:

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

Not applicable
Author

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

Message was edited by: ramin006

Jason_Michaelides
Luminary Alumni
Luminary Alumni

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

Not applicable
Author

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