Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
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;
Hi Nisha,
Thank you very much.
You are a life saver.
It's working as intended.
Kind regards,
Andy
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
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
Hi Nisha,
Thank you once again.
Kind regards,
Andy