topic Re: avg in QlikView

avg

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

I have an incoming file from invoice.txt

so i need something like in my load script..

Invoice id

invoice amount

invoice new amount

invoice type

Load *,

Round(Avg([invoice amount]), 1/100) as [Invoiceactuals]

the above works

but now i need something like

Load *,

if(invoice_type = 'processed' ,Round(Avg([invoice amount]), 1/100 ,Round(Avg([invoice new amount] as [Invoiceactuals] - this is not working

Re: avg

Anil_Babu_Samineni — Fri, 17 Mar 2017 09:57:14 GMT

May be this?

Sample:

Load Invoice id,

invoice amount,

invoice new amount,

invoice type]

From <Data Source>;

Noconcatenate

Load *,

Round(Avg([invoice amount]), 1/100) as [Invoiceactuals]

Resident Sample

Group By [Invoice id], [invoice amount], [invoice new amount], [invoice type]

Order By [Invoice id];

Left Join

Load *,

if(invoice_type = 'processed' ,Round(Avg([invoice amount]), 1/100) ,Round(Avg([invoice new amount])) as [Invoiceactuals]

Resident Sample;

Drop Table Sample;

Re: avg

sravanthialuval — Fri, 17 Mar 2017 10:17:34 GMT

Try this,

Load *,Round(Avg([invoice amount]), 1/100) as [Invoiceactuals],

if(invoice_type = 'processed' ,Round(Avg([invoice amount]), 1/100) ,Round(Avg([invoice new amount]) as [Invoiceactuals] Group by invoice id,invoice amount,invoice new amount, invoice type Order by [Invoice id];

Load

[Invoice id]

[invoice amount]

[invoice new amount]

[invoice type]

from source;

Re: avg

Kushal_Chawda — Fri, 17 Mar 2017 10:28:38 GMT

Data:

[Invoice id],

sum([invoice amount]) as [invoice amount],

sum([invoice new amount]) as [invoice new amount],

round(Avg(if([invoice type]='processed',[invoice amount])),0.01) as AvgInvoiceProcessed,

round(Avg(if([invoice type]<>'processed',[invoice amount]))) as AvgInvoiceActual,

[invoice type]

FROM Source

Group by

[Invoice id],

[invoice type];

Final:

noconcatenate

load [Invoice id],

[invoice amount],

[invoice new amount],

if([invoice type]='processed',AvgInvoiceProcessed,AvgInvoiceActual) as InVoiceActual,

[invoice type]

resident Data;

drop table Data;