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: 
andymanu
Creator II
Creator II

Classify data in to funded and unfunded based on a condition

Hi All,

I got a data set which looks like below;

Customer_IDFunding_ArrangementHoursEmployee
100CWH3David
100CWH3Andy
101CWH1.5David
101CWH1.5Andy
102HACC1David
103HACC2David
103HACC2Andy
103HACC2Nick
105SCO2David
104SCO3Andy
104SCO3David
104SCO3Nick

 

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_IDFunding_ArrangementEmployeeFunded HoursUnfunded Hours
100CWHDavid30
100CWHAndy30
101CWHDavid1.50
101CWHAndy1.50
102HACCDavid10
103HACCDavid20
103HACCAndy02
103HACCNick02
104SCOAndy30
104SCODavid03
104SCONick03
105SCODavid20

 

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

Labels (1)
1 Solution

Accepted Solutions
nisha_rai
Creator II
Creator II

Hi,

Please use the below code, May it helpsClasified Data.PNG

 

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;

 

 

View solution in original post

5 Replies
nisha_rai
Creator II
Creator II

Hi,

Please use the below code, May it helpsClasified Data.PNG

 

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;

 

 

andymanu
Creator II
Creator II
Author

Hi Nisha,

Thank you very much.

You are a life saver.

It's working as intended.

Kind regards,

Andy

andymanu
Creator II
Creator II
Author

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

nisha_rai
Creator II
Creator II

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

andymanu
Creator II
Creator II
Author

Hi Nisha,

Thank you once again.

Kind regards,

Andy