Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
manozpph
Creator
Creator

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

 

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....

1 Solution

Accepted Solutions
quddus_mohiuddi
Partner - Contributor III
Partner - Contributor III

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

View solution in original post

9 Replies
quddus_mohiuddi
Partner - Contributor III
Partner - Contributor III

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
Partner - Specialist III
Partner - Specialist III

Hi Mano,

Please Provide sample Excel.

Thanks,

Arvind Patil

manozpph
Creator
Creator
Author

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
Creator
Creator
Author

Can you please check the sample data above.Thanks

manozpph
Creator
Creator
Author

Provided.Pl chk.

quddus_mohiuddi
Partner - Contributor III
Partner - Contributor III

Hi,

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

Regards,

Quddus

manozpph
Creator
Creator
Author

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
Partner - Contributor III
Partner - Contributor III

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
Creator
Creator
Author

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