topic Re: How to write Logic to Concatenate Historic and Current data!! in QlikView

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

manozpph — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: I have 2 Excel Files with Historic and Current Data and I want to concatenate

quddus_mohiuddi — Fri, 13 Jul 2018 05:42:55 GMT

Hi,

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

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

arvind_patil — Fri, 13 Jul 2018 06:18:01 GMT

Hi Mano,

Please Provide sample Excel.

Thanks,

Arvind Patil

Re: I have 2 Excel Files with Historic and Current Data and I want to concatenate

manozpph — Fri, 13 Jul 2018 15:41:24 GMT

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

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

manozpph — Fri, 13 Jul 2018 16:14:26 GMT

Can you please check the sample data above.Thanks

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

manozpph — Mon, 16 Jul 2018 04:12:02 GMT

Provided.Pl chk.

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

quddus_mohiuddi — Mon, 16 Jul 2018 07:06:39 GMT

Hi,

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

Regards,

Quddus

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

manozpph — Mon, 16 Jul 2018 19:47:25 GMT

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

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

quddus_mohiuddi — Tue, 17 Jul 2018 05:16:27 GMT

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

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

manozpph — Tue, 17 Jul 2018 13:55:42 GMT

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