avg

[raadwiptec]

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

[Anil_Babu_Samineni]

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;

[sravanthialuval]

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;

[Kushal_Chawda]

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;