Create New Field on Script based on calculation

Thu, 12 Dec 2013 02:25:52 GMT

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

Re: Create New Field on Script based on calculation

Thu, 12 Dec 2013 03:06:06 GMT

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;

topic Create New Field on Script based on calculation in QlikView

Create New Field on Script based on calculation

Re: Create New Field on Script based on calculation