How to write Logic to Concatenate Historic and Current data!!

[manozpph]

Hi Folks, Please go through the attached sample excel sheets. Mentioning Sheet1 has historic and sheet2 has current data.Please Initiate me Steps to get the Output expecting below....!

Notes:

IF Process Followed ='Yes" has 10 rows and Inciden No  has Total Rows with records=10/10=1(100%)

I have given may and june dates are fake with no values.

Current data doesn't have 'Gol' or 'Act'.

Code  for Calculations:

Below  2 tables  Loaded for  dividing purpose from Current Data

Total Rows :

with  Count(Incident no)  as Total Rows

From-------- Current data file;

Totalofrowswithyes:

Count(Incident no) as Totalofrowswithyes

From-------Current data file;

For  Actual Calculations:

Total of rows with yes/Total Rows gives Actual Values. This value I assign to variable.

Total Rows=10,RowswithYes=10 that give 1 means Actual =100%

Incase 1 row  If Process followed ='No', it should be counted like 9/10=0.9.

I want to see OUTPUT:It can be concatenated historical and current data and presents  Values replicating with actual calculations below,

IT Table:

-----------   

Calendardate            Gol                  Act

01/15/2018               95%              100%

02/15/2018               95%              100%

03/15/2018               95%              100%

04/15/2018                95%              100%

05/01/2018                95%              0%

06/01/2018                 95%              0%

06/01/2018                 95%            

5/01/2018                 95%              

04/01/2018                 95%              0.9(can show in %)   like that....

[quddus_mohiuddi]

Hi,

Can u tell me that are there multiple records of Incident no and Process exists on one date in Current data tabel?

[arvind_patil]

Hi Mano,

Please Provide sample Excel.

Thanks,

Arvind Patil

[manozpph]

Yes,Incident no has multiple records,Date has multiple records in historica and current data from jan-april/2018.

We can add fake records for For May and June/2018 to view Latest data.

Thanks

[manozpph]

Can you please check the sample data above.Thanks

[manozpph]

Provided.Pl chk.

[quddus_mohiuddi]

Hi,

Please see the attachment, is this your requirement. i have performed your required calculation on your given Current Data only.

Regards,

Quddus

[manozpph]

Hi,Thank you for working on it. Unfortunately  I'm unable to open the application from my end.

If you don't mind can u copy n paste the code here. I will write in my machine.

Thanks again...!

[quddus_mohiuddi · Accepted solution]

Hi,

Always welcome,

check this code if this is your requirement so just need to concatenate historical data into this.

Temp_CurrentData:

LOAD Date,

     [Incident No],

     [Process Followed],

     Count(RowNo(TOTAL)) as Count

FROM

(biff, embedded labels, table is Sheet2$)

Group By

Date,

    [Incident No],

    [Process Followed];

Left join

LOAD

     Sum(Count) as Count_New

Resident Temp_CurrentData ;

CurrentData:

LOAD Date as Metric_Date,

     [Incident No],

//     [Process Followed],

     Num(if([Process Followed]='Yes',Count_New,Count_New-1)/Count_New,'0.00%') as Actual

//     Count_New

Resident Temp_CurrentData ;

Regards,

Quddus

[manozpph]

Ok,Thank u Very----much for your help.