Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Say I have a table like below and in the attachment, but with millions of household IDs. I'd like to create a new field, called 'n1 Viewership - High/Medium/Low', based on the calculation of time spent of Network n1. If the average time spent is on the top 1/3, return to the value of 'High' of the new field, middle 1/3 'Medium' and bottom 1/3 'Low'. If no viewership of the n1 network, then 'NA'. How do I do it? Thanks in advance!
HH | Network | Time spent |
hh1 | n1 | 20 |
hh2 | n1 | 10 |
hh3 | n2 | 30 |
hh4 | n1 | 15 |
hh5 | n2 | 33 |
hh6 | n2 | 14 |
hh7 | n1 | 22 |
hh8 | n2 | 50 |
hh9 | n1 | 23 |
hh10 | n1 | 44 |
hh11 | n2 | 5 |
hh12 | n2 | 12 |
hh13 | n1 | 55 |
hh14 | n1 | 42 |
hh15 | n1 | 33 |
hh16 | n1 | 12 |
hh17 | n2 | 30 |
Please try like below:
TEMP:
LOAD HH, NetWork, TimeSpent FROM SOURCE;
// Calculate Avg Value: Sum ([Time spent])/Count (DISTINCT HH)
T_AVG:
LOAD COUNT(DISTINCT HH) AS HH_CNT, Sum([Time Spent]) AS SUM_TIME_SPENT
Resident TEMP Where HH='n1';
Let vAvg = num(peek('SUM_TIME_SPENT')/peek('HH_CNT'),'###0.00') ;
Drop Table T_AVG;
FINAL:
LOAD *,
IF(Network='n1' , IF([Time Spent] > ($(vAvg)*2)/3 , 'High' , IF([Time Spent] < $(vAvg)/3 ,'Low', 'Medium' ) ) , 'NA') AS N1_VIEWERSHIP
Resident TEMP;
DROP Table TEMP;