Need help in this scripting
i have two tables
table1:
LOAD
[HM Measure Code],
[HM Measure Code Description],
[HM Measure User Type],
[HM Measure Exceptions],
[HM Measure Exception %],
[HM Measure Population],
[HM Measure Date],
Date,
[HM Group],
[HM Measure Control],
[HM Weight]
FROM
[..\ERP\Health Measures.qvd]
(qvd);
outer join(table1)
table2:
load Distinct
[HM Measure Code],
variablefields,
data
Resident tableres;
for Each var in 7,14,21,28,35,42,49,56,63,70,77,84,91
if var = 7 then
NoConcatenate
table1_TEMP:
LOAD distinct
[HM Measure Code],
variablefields,
count( [HM Measure Site Code]) as HM_MEASURE_SITE_COUNT,
sum(if(Upper(Text([HM Measure Control]))<> 'N',[HM Measure Population])) as HM_MEASURE_POPULATION,
sum(if(Upper(Text([HM Measure Control]))<> 'N',[HM Measure Exceptions])) as HM_MEASURE_EXCEPTION,
$(var) as LoopBack
Resident table1
where match ([HM Measure Code] ,'EM08','ET06',
'EM07','EM09','PM21')>0 and num([HM Measure Date])>=num(today()-$(var))
group By [HM Measure Code]*variablefields,$(var);
tablefinal:
LOAD LoopBack,
[HM Measure Code],
data,
//[HM Measure Code]*data as HM_Measure_code_new,
if(HM_MEASURE_SITE_COUNT=0,0,
if(HM_MEASURE_POPULATION<>0,
1-(HM_MEASURE_EXCEPTION/HM_MEASURE_POPULATION),0)) as TEMP_1
Resident table1_TEMP;
drop Table table1_TEMP;
i have values for variablefields are like EM08_F,'ETo6_F' that is similar to codes
now what i want that tablefinal Temp_1 expression should calculate for every [HM Measure Code] and after calculation each expression for evry mesaure code it will multiply
variable field that has similar name to measure code .
like wise it will calculate for all the four codes and will add them
could you please help ?
