Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Now Live: Qlik Sense SaaS Simplified Authoring – Analytics Creation for Everyone: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
raadwiptec
Creator II
Creator II

avg

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

3 Replies
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;

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
sravanthialuval
Creator
Creator

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;