topic Re: Classify data in to funded and unfunded based on a condition in App Development

Classify data in to funded and unfunded based on a condition

andymanu — Sat, 16 Nov 2024 19:06:05 GMT

Hi All,

I got a data set which looks like below;

Customer_ID	Funding_Arrangement	Hours	Employee
100	CWH	3	David
100	CWH	3	Andy
101	CWH	1.5	David
101	CWH	1.5	Andy
102	HACC	1	David
103	HACC	2	David
103	HACC	2	Andy
103	HACC	2	Nick
105	SCO	2	David
104	SCO	3	Andy
104	SCO	3	David
104	SCO	3	Nick

Now my requirement is, i want to classify the "Hours" data into two categories named, "Funded Hours" and "Unfunded Hours".

This is to be done based on the "Funding_Arrangement" field and the "Customer_ID" field.
Basically, any data row comes as 'CWH' under "Funding_Arrangement", will consider as "Funded Hours". However, anything other than 'CWH' (Funding_Arrangement) should classify based on the "Customer_ID" field. For each unique "Customer_ID's, the first data row should consider as the funded and the remaining (if any) as the unfunded hours.

Please refer, Customer_ID = '103'. The "Funding_Arrangement" is 'HACC' thus, the first row (where the Employee is 'David', will consider as the funded hours row and the remaining two rows (for Andy and Nick) as the unfunded hours.

The final output should looks something like below;

Customer_ID	Funding_Arrangement	Employee	Funded Hours	Unfunded Hours
100	CWH	David	3	0
100	CWH	Andy	3	0
101	CWH	David	1.5	0
101	CWH	Andy	1.5	0
102	HACC	David	1	0
103	HACC	David	2	0
103	HACC	Andy	0	2
103	HACC	Nick	0	2
104	SCO	Andy	3	0
104	SCO	David	0	3
104	SCO	Nick	0	3
105	SCO	David	2	0

Appreciate your feedback in advance.

A flag can be used to categorized the "Funding_Arrangement" but not sure whether the "Grouping" function can be used to classify the hours as "Funded Hours" and "Unfunded Hours".

Kind regards,

Andy

Re: Classify data in to funded and unfunded based on a condition

nisha_rai — Tue, 18 Feb 2020 11:15:53 GMT

Hi,

Please use the below code, May it helps

Test:
LOAD
Customer_ID,
Funding_Arrangement,
"Hours",
Employee
FROM [lib://AttachedFiles/Funded Hours vs Unfunded Hours Data.xlsx]
(ooxml, embedded labels, table is Data);

Test1:
Load *,
If(FundedHours=0,"Hours",0) as UnfundedHours;
Load *,
if(peek('Customer_ID')<>Customer_ID or Funding_Arrangement='CWH' ,"Hours",0) as FundedHours

Resident Test
;

Drop Table Test;

exit Script;

Re: Classify data in to funded and unfunded based on a condition

andymanu — Tue, 18 Feb 2020 11:57:59 GMT

Hi Nisha,

Thank you very much.

You are a life saver.

It's working as intended.

Kind regards,

Andy

Re: Classify data in to funded and unfunded based on a condition

andymanu — Tue, 18 Feb 2020 12:06:44 GMT

Hi Nisha,

Just a small request too.

Could you please explain the logic behind your code? That would be a very good learning process for me.

It's working, but there is a gap in my understanding of your logic.

Thank you in advance.

Kind regards,

Andy

Re: Classify data in to funded and unfunded based on a condition

nisha_rai — Tue, 18 Feb 2020 12:17:41 GMT

Hi,

I am using the peek function ,when peek of customer id is not match with that row customero id or funding gape is equal to cwh then only it will give hours,else give 0,when any of the condition true u get the hours

Re: Classify data in to funded and unfunded based on a condition

andymanu — Tue, 18 Feb 2020 12:55:49 GMT

Hi Nisha,

Thank you once again.

Kind regards,

Andy