How to insert expression in script

silvia_assuncao — Wed, 25 Nov 2020 16:16:04 GMT

Hi All!

I'm trying to insert an expression in my script, but I'm find it dificult to do it. could anyone help me?

Below is the expression:

(Sum(if([Registry ID]=10 , [Resource Account Total Amount with Taxes]/100))
+
Sum(if([Registry ID]='70' and [Adjust Type]='R' and [Adjust Signal]='-',[Adjust Amount]*(-1)/100))
+
Sum(if([Registry ID]='70' and [Adjust Type]='R' and [Adjust Signal]='+',[Adjust Amount]/100))
+
Sum(if([Registry ID]='50' and [Discount Type]='R' and [Discount Signal]='-',[Discount Amount]*(-1)/100))
+
Sum(if([Registry ID]='60' and [Plan Type]='R' ,[Plan Amount with Taxes]/100)))
+
(Sum(Aggr(If(Count(Distinct(if(left([Unique Resource ID],1)<>' ' and left([Unique Resource ID],1)<>'' and [Registry ID]=60 or [Registry ID]=10,[Unique Resource ID]))) > 0,
Sum(TOTAL <[Unique Account ID]> Aggr(
( Sum(If([Adjust Type]='C' and [Adjust Signal]='-',([Adjust Amount]*(-1)/100))))
+ Sum(If([Adjust Type]='C' and [Adjust Signal]='+',([Adjust Amount]/100)))
+Sum(If([Discount Type]='C' and [Discount Signal]='-',([Discount Amount]*(-1))/100))
+ Sum(If([Plan Type]='C' and [Registry ID] = '60',[Plan Amount with Taxes])/100)
, [Unique Account ID], [Unique Resource ID]))/
Sum(TOTAL <[Unique Account ID]> Aggr(Count(Distinct(if(left([Unique Resource ID],1)<>' ' and left([Unique Resource ID],1)<>'' and [Registry ID]=60 or [Registry ID]=10,[Unique Resource ID])))
, [Unique Account ID], [Unique Resource ID]))), [Unique Account ID], [Unique Resource ID])))

and below the last scrit:

Outer Join(Fact)
LOAD @1:2 AS [Registry ID],
//Mid(@3:n,001,012) AS [Sequential Recording Control],
Mid(@3:n,013,025) AS [Unique Account ID],
Mid(@3:n,038,008) AS [Emission Date],
Mid(@3:n,046,006) AS [Reference Month],
Mid(@3:n,052,008) AS [Due Date],
Mid(@3:n,060,015) AS [Client Code],
Mid(@3:n,075,013) AS [Total Amount],
Mid(@3:n,088,012) AS [Total Records],
Mid(@3:n,100,013) AS [Total Amount Records 10],
Mid(@3:n,113,009) AS [Total Records 10],
Mid(@3:n,122,009) AS [Total Records 20],
Mid(@3:n,131,013) AS [Total Amount Records 30],
Mid(@3:n,144,009) AS [Total Records 30],
Mid(@3:n,153,013) AS [Total Amount Records 40],
Mid(@3:n,166,009) AS [Total Records 40],
Mid(@3:n,175,001) AS [Signal of Total Records 50],
Mid(@3:n,176,013) AS [Total Amount Records 50],
Mid(@3:n,189,009) AS [Total Records 50],
Mid(@3:n,198,013) AS [Total Amount Records 60],
Mid(@3:n,211,009) AS [Total Records 60],
Mid(@3:n,220,001) AS [Signal of Total Records 70],
Mid(@3:n,221,013) AS [Total Amount Records 70],
Mid(@3:n,234,009) AS [Total Records 70],
Mid(@3:n,243,013) AS [Total Amount Records 80],
Mid(@3:n,256,009) AS [Total Records 80],
Mid(@3:n,323,025) AS [Open Field to Carrier]

FROM $(FebrabanFiles)
(fix, codepage is 1252)
WHERE(@1:2 = $(TraillerID));

topic How to insert expression in script in QlikView

How to insert expression in script