Merge multiple records to one

[robhagat]

Hi,

I am creating a dashboard where my input data is from a QVD file which returns multiple records  i.e. 30 records for 30 days for each meters. I have to create a table by loading the qvd file and finding the average of meter reads [sum(ACTUAL_COUNT)/sum(EXP_COUNT)*30] and returning only one record for each meter. I am trying to execute the below code but it is giving error. Please suggest the way forward.

Please find attached my data from the QVD.

DQ:

LOAD

    MeterID,

    MR_Date,

    //READ_FREQUENCY,

    EXP_COUNT,

    ACTUAL_COUNT,

    //DataQuality,

    BulkType

FROM [lib://Meter Journey/MeterReads_R30Ds.qvd]

(qvd)

where BulkType='NR-PROJ';

DataQuality:

load *,

if(Count(Meterid)>0,sum(ACTUAL_COUNT)/sum(EXP_COUNT)*30,'NA') as DQ%

resident DQ;

drop table DQ;

Regards,

Romila.

[agigliotti · Accepted solution]

maybe this:

DataQuality:

load

     Meterid,

     count(Meterid) as TOT_Meterid,

     sum(EXP_COUNT) * 30 as TOT_EXP_COUNT,

     sum(ACTUAL_COUNT) as TOT_ACTUAL_COUNT

resident DQ group by Meterid;

I hope it helps.

[robhagat]

Thanks. It worked