Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello All,
Below is the script for Hierarchy,i want to convert it into one block of script with loops.
fields may vary for each month depending on the organization levels,one month levels may be 12 ,
next month levels may be 16.
if i try to make it one script ,it gives me error 'field not found' for levels not in that particular month.
Hierarchy1:
Hierarchy(WORKER_ID,[SUPERVISOR_ID],NAME,,NAME,PATH,'/',HIERARCHY)
LOAD
DISTINCT WORKER_ID, [SUPERVISOR_ID],NAME,MonthYear,recno() as recno
Resident WorkerHierarchy
where MonthYear = $(vPMonth1);
Hierarchy_DEC_2015:
LOAD WORKER_ID,
[SUPERVISOR_ID],
NAME,
MonthYear,
recno,
PATH,
HIERARCHY,
if ([NAME1] = Peter,[ NAME1], 'Unknown') asWorker,
if(NAME1= Peter and NAME3= 'Charles','Charles',
if (NAME1= Peter,[NAME2], 'Unknown')) asWorker0,
if(NAME1= Peter and NAME4= Adrian,Adrian,
if(NAME3= 'Charles',[NAME4],
if(NAME1= Peter and NAME3<> 'Charles' ,[NAME3],
if(NAME1<> Peter or NAME3<> 'Charles','Unknown')))) asWorker1,
if(NAME4= Adrian or NAME3= 'Charles',NAME5],
if(NAME1= Peter and NAME4<> Adrian,[NAME4],
if(NAME1<> Peter or NAME4<> Adrian or NAME3<> 'Charles','Unknown'))) asWorker2,
if(NAME4= Adrian or NAME3= 'Charles',NAME6],
if(NAME4<> Adrian or NAME3<> 'Charles',NAME5])) asWorker3,
if(NAME4= Adrian or NAME3= 'Charles',NAME7],
if(NAME4<> Adrian or NAME3<> 'Charles',NAME6])) asWorker4,
if(NAME4= Adrian or NAME3= 'Charles',[NAME8],
if(NAME4<> Adrian or NAME3<> 'Charles',NAME7])) asWorker5,
if(NAME4= Adrian or NAME3= 'Charles',NAME9],
if(NAME4<> Adrian or NAME3<> 'Charles',[NAME8])) asWorker6,
if(NAME4= Adrian or NAME3= 'Charles',NAME10],
if(NAME4<> Adrian or NAME3<> 'Charles',NAME9])) asWorker7,
if(NAME4= Adrian or NAME3= 'Charles',[NAME11],
if(NAME4<> Adrian or NAME3<> 'Charles',NAME10])) asWorker8,
if(NAME4= Adrian or NAME3= 'Charles',NAME12],
if(NAME4<> Adrian or NAME3<> 'Charles',[NAME11])) asWorker9,
if(NAME4= Adrian or NAME3= 'Charles',NAME13],
if(NAME4<> Adrian or NAME3<> 'Charles',NAME12])) asWorker10,
if(NAME4= Adrian or NAME3= 'Charles',[NAME14],
if(NAME4<> Adrian or NAME3<> 'Charles',NAME13])) asWorker11,
if(NAME4<> Adrian or NAME3<> 'Charles',[NAME14]) asWorker12
Resident Hierarchy1;
Drop Table Hierarchy1;
STORE Hierarchy_DEC_2015 into $(QVDPath) Hierarchy_DEC 2015.qvd;
Drop Table Hierarchy_DEC_2015;
Hierarchy2:
Hierarchy(WORKER_ID,[SUPERVISOR_ID],NAME,,NAME,PATH,'/',HIERARCHY)
LOAD
DISTINCT WORKER_ID, [SUPERVISOR_ID],NAME,MonthYear,recno() as recno
Resident WorkerHierarchy
where MonthYear = $(vPMonth2);
Hierarchy_NOV_2015:
LOAD WORKER_ID,
[SUPERVISOR_ID],
NAME,
MonthYear,
recno,
PATH,
HIERARCHY,
if (NAME1= Peter,NAME1], 'Unknown') asWorker,
if(NAME1= Peter and NAME3= 'Charles','Charles',
if (NAME1= Peter,[NAME2], 'Unknown')) asWorker0,
if(NAME1= Peter and NAME4= Adrian,Adrian,
if(NAME3= 'Charles',[NAME4],
if(NAME1= Peter and NAME3<> 'Charles' ,[NAME3],
if(NAME1<> Peter or NAME3<> 'Charles','Unknown')))) asWorker1,
if(NAME4= Adrian or NAME3= 'Charles',NAME5],
if(NAME1= Peter and NAME4<> Adrian,[NAME4],
if(NAME1<> Peter or NAME4<> Adrian or NAME3<> 'Charles','Unknown'))) asWorker2,
if(NAME4= Adrian or NAME3= 'Charles',NAME6],
if(NAME4<> Adrian or NAME3<> 'Charles',NAME5])) asWorker3,
if(NAME4= Adrian or NAME3= 'Charles',NAME7],
if(NAME4<> Adrian or NAME3<> 'Charles',NAME6])) asWorker4,
if(NAME4= Adrian or NAME3= 'Charles',[NAME8],
if(NAME4<> Adrian or NAME3<> 'Charles',NAME7])) asWorker5,
if(NAME4= Adrian or NAME3= 'Charles',NAME9],
if(NAME4<> Adrian or NAME3<> 'Charles',[NAME8])) asWorker6,
if(NAME4= Adrian or NAME3= 'Charles',NAME10],
if(NAME4<> Adrian or NAME3<> 'Charles',NAME9])) asWorker7,
if(NAME4= Adrian or NAME3= 'Charles',[NAME11],
if(NAME4<> Adrian or NAME3<> 'Charles',NAME10])) asWorker8,
if(NAME4= Adrian or NAME3= 'Charles',NAME12],
if(NAME4<> Adrian or NAME3<> 'Charles',[NAME11])) asWorker9,
if(NAME4= Adrian or NAME3= 'Charles',NAME13],
if(NAME4<> Adrian or NAME3<> 'Charles',NAME12])) asWorker10,
if(NAME4= Adrian or NAME3= 'Charles',[NAME14],
if(NAME4<> Adrian or NAME3<> 'Charles',NAME13])) asWorker11,
if(NAME4<> Adrian or NAME3<> 'Charles',[NAME14]) asWorker12
Resident Hierarchy2;
Drop Table Hierarchy2;
STORE Hierarchy_NOV_2015 into $(QVDPath) Hierarchy_NOV 2015.qvd;
Drop Table Hierarchy_NOV_2015;
Hierarchy3:
Hierarchy(WORKER_ID,[SUPERVISOR_ID],NAME,,NAME,PATH,'/',HIERARCHY)
LOAD
DISTINCT WORKER_ID, [SUPERVISOR_ID],NAME,MonthYear,recno() as recno
Resident WorkerHierarchy
where MonthYear = $(vPMonth3);
Hierarchy_OCT_2015:
LOAD WORKER_ID,
[SUPERVISOR_ID],
NAME,
MonthYear,
recno,
PATH,
HIERARCHY,
if (NAME1= Peter,NAME1], 'Unknown') asWorker,
if(NAME1= Peter and NAME3= 'Charles','Charles',
if (NAME1= Peter,[NAME2], 'Unknown')) asWorker0,
if(NAME1= Peter and NAME4= Adrian,Adrian,
if(NAME3= 'Charles',[NAME4],
if(NAME1= Peter and NAME3<> 'Charles' ,[NAME3],
if(NAME1<> Peter or NAME3<> 'Charles','Unknown')))) asWorker1,
if(NAME4= Adrian or NAME3= 'Charles',NAME5],
if(NAME1= Peter and NAME4<> Adrian,[NAME4],
if(NAME1<> Peter or NAME4<> Adrian or NAME3<> 'Charles','Unknown'))) asWorker2,
if(NAME4= Adrian or NAME3= 'Charles',NAME6],
if(NAME4<> Adrian or NAME3<> 'Charles',NAME5])) asWorker3,
if(NAME4= Adrian or NAME3= 'Charles',NAME7],
if(NAME4<> Adrian or NAME3<> 'Charles',NAME6])) asWorker4,
if(NAME4= Adrian or NAME3= 'Charles',[NAME8],
if(NAME4<> Adrian or NAME3<> 'Charles',NAME7])) asWorker5,
if(NAME4= Adrian or NAME3= 'Charles',NAME9],
if(NAME4<> Adrian or NAME3<> 'Charles',[NAME8])) asWorker6,
if(NAME4= Adrian or NAME3= 'Charles',NAME10],
if(NAME4<> Adrian or NAME3<> 'Charles',NAME9])) asWorker7,
if(NAME4= Adrian or NAME3= 'Charles',[NAME11],
if(NAME4<> Adrian or NAME3<> 'Charles',NAME10])) asWorker8,
if(NAME4= Adrian or NAME3= 'Charles',NAME12],
if(NAME4<> Adrian or NAME3<> 'Charles',[NAME11])) asWorker9,
if(NAME4= Adrian or NAME3= 'Charles',NAME13],
if(NAME4<> Adrian or NAME3<> 'Charles',NAME12])) asWorker10,
if(NAME4= Adrian or NAME3= 'Charles',[NAME14],
if(NAME4<> Adrian or NAME3<> 'Charles',NAME13])) asWorker11,
if(NAME4<> Adrian or NAME3<> 'Charles',[NAME14]) asWorker12
Resident Hierarchy3;
Drop Table Hierarchy3;
STORE Hierarchy_OCT_2015 into $(QVDPath) Hierarchy_OCT 2015.qvd;
Drop Table Hierarchy_OCT_2015;
Hierarchy4:
Hierarchy(WORKER_ID,[SUPERVISOR_ID],NAME,,NAME,PATH,'/',HIERARCHY)
LOAD
DISTINCT WORKER_ID, [SUPERVISOR_ID],NAME,MonthYear,recno() as recno
Resident WorkerHierarchy
where MonthYear = $(vPMonth4);
Hierarchy_SEP_2015:
LOAD WORKER_ID,
[SUPERVISOR_ID],
NAME,
MonthYear,
recno,
PATH,
HIERARCHY,
if (NAME1= Peter,NAME1], 'Unknown') asWorker,
if(NAME1= Peter and NAME3= 'Charles','Charles',
if (NAME1= Peter,[NAME2], 'Unknown')) asWorker0,
if(NAME1= Peter and NAME4= Adrian,Adrian,
if(NAME3= 'Charles',[NAME4],
if(NAME1= Peter and NAME3<> 'Charles' ,[NAME3],
if(NAME1<> Peter or NAME3<> 'Charles','Unknown')))) asWorker1,
if(NAME4= Adrian or NAME3= 'Charles',NAME5],
if(NAME1= Peter and NAME4<> Adrian,[NAME4],
if(NAME1<> Peter or NAME4<> Adrian or NAME3<> 'Charles','Unknown'))) asWorker2,
if(NAME4= Adrian or NAME3= 'Charles',NAME6],
if(NAME4<> Adrian or NAME3<> 'Charles',NAME5])) asWorker3,
if(NAME4= Adrian or NAME3= 'Charles',NAME7],
if(NAME4<> Adrian or NAME3<> 'Charles',NAME6])) asWorker4,
if(NAME4= Adrian or NAME3= 'Charles',[NAME8],
if(NAME4<> Adrian or NAME3<> 'Charles',NAME7])) asWorker5,
if(NAME4= Adrian or NAME3= 'Charles',NAME9],
if(NAME4<> Adrian or NAME3<> 'Charles',[NAME8])) asWorker6,
if(NAME4= Adrian or NAME3= 'Charles',NAME10],
if(NAME4<> Adrian or NAME3<> 'Charles',NAME9])) asWorker7,
if(NAME4= Adrian or NAME3= 'Charles',[NAME11],
if(NAME4<> Adrian or NAME3<> 'Charles',NAME10])) asWorker8,
if(NAME4= Adrian or NAME3= 'Charles',NAME12],
if(NAME4<> Adrian or NAME3<> 'Charles',[NAME11])) asWorker9,
if(NAME4= Adrian or NAME3= 'Charles',NAME13],
if(NAME4<> Adrian or NAME3<> 'Charles',NAME12])) asWorker10,
if(NAME4= Adrian or NAME3= 'Charles',[NAME14],
if(NAME4<> Adrian or NAME3<> 'Charles',NAME13])) asWorker11,
if(NAME4= Adrian or NAME3= 'Charles',NAME15],
if(NAME4<> Adrian or NAME3<> 'Charles',[NAME14])) asWorker12,
if(NAME4= Adrian or NAME3= 'Charles',NAME16],
if(NAME4<> Adrian or NAME3<> 'Charles',NAME15])) asWorker13,
if(NAME4<> Adrian or NAME3<> 'Charles',NAME16]) asWorker14
Resident Hierarchy4;
Drop Table Hierarchy4;
STORE Hierarchy_SEP_2015 into $(QVDPath) Hierarchy_SEP 2015.qvd;
Drop Table Hierarchy_SEP_2015;
Hierarchy5:
Hierarchy(WORKER_ID,[SUPERVISOR_ID],NAME,,NAME,PATH,'/',HIERARCHY)
LOAD
DISTINCT WORKER_ID, [SUPERVISOR_ID],NAME,MonthYear,recno() as recno
Resident WorkerHierarchy
where MonthYear = $(vPMonth5);
Hierarchy_AUG_2015:
LOAD WORKER_ID,
[SUPERVISOR_ID],
NAME,
MonthYear,
recno,
PATH,
HIERARCHY,
if (NAME1= Peter,NAME1], 'Unknown') asWorker,
if(NAME1= Peter and NAME3= 'Charles','Charles',
if (NAME1= Peter,[NAME2], 'Unknown')) asWorker0,
if(NAME1= Peter and NAME4= Adrian,Adrian,
if(NAME3= 'Charles',[NAME4],
if(NAME1= Peter and NAME3<> 'Charles' ,[NAME3],
if(NAME1<> Peter or NAME3<> 'Charles','Unknown')))) asWorker1,
if(NAME4= Adrian or NAME3= 'Charles',NAME5],
if(NAME1= Peter and NAME4<> Adrian,[NAME4],
if(NAME1<> Peter or NAME4<> Adrian or NAME3<> 'Charles','Unknown'))) asWorker2,
if(NAME4= Adrian or NAME3= 'Charles',NAME6],
if(NAME4<> Adrian or NAME3<> 'Charles',NAME5])) asWorker3,
if(NAME4= Adrian or NAME3= 'Charles',NAME7],
if(NAME4<> Adrian or NAME3<> 'Charles',NAME6])) asWorker4,
if(NAME4= Adrian or NAME3= 'Charles',[NAME8],
if(NAME4<> Adrian or NAME3<> 'Charles',NAME7])) asWorker5,
if(NAME4= Adrian or NAME3= 'Charles',NAME9],
if(NAME4<> Adrian or NAME3<> 'Charles',[NAME8])) asWorker6,
if(NAME4= Adrian or NAME3= 'Charles',NAME10],
if(NAME4<> Adrian or NAME3<> 'Charles',NAME9])) asWorker7,
if(NAME4= Adrian or NAME3= 'Charles',[NAME11],
if(NAME4<> Adrian or NAME3<> 'Charles',NAME10])) asWorker8,
if(NAME4= Adrian or NAME3= 'Charles',NAME12],
if(NAME4<> Adrian or NAME3<> 'Charles',[NAME11])) asWorker9,
if(NAME4= Adrian or NAME3= 'Charles',NAME13],
if(NAME4<> Adrian or NAME3<> 'Charles',NAME12])) asWorker10,
if(NAME4= Adrian or NAME3= 'Charles',[NAME14],
if(NAME4<> Adrian or NAME3<> 'Charles',NAME13])) asWorker11,
if(NAME4= Adrian or NAME3= 'Charles',NAME15],
if(NAME4<> Adrian or NAME3<> 'Charles',[NAME14])) asWorker12,
if(NAME4= Adrian or NAME3= 'Charles',NAME16],
if(NAME4<> Adrian or NAME3<> 'Charles',NAME15])) asWorker13,
if(NAME4<> Adrian or NAME3<> 'Charles',NAME16]) asWorker14
Resident Hierarchy5;
Drop Table Hierarchy5;
STORE Hierarchy_AUG_2015 into $(QVDPath) Hierarchy_AUG 2015.qvd;
Drop Table Hierarchy_AUG_2015;
Hierarchy6:
Hierarchy(WORKER_ID,[SUPERVISOR_ID],NAME,,NAME,PATH,'/',HIERARCHY)
LOAD
DISTINCT WORKER_ID, [SUPERVISOR_ID],NAME,MonthYear,recno() as recno
Resident WorkerHierarchy
where MonthYear = $(vPMonth6);
Hierarchy_JUL_2015:
LOAD WORKER_ID,
[SUPERVISOR_ID],
NAME,
MonthYear,
recno,
PATH,
HIERARCHY,
if (NAME1= Peter,NAME1], 'Unknown') asWorker,
if(NAME1= Peter and NAME3= 'Charles','Charles',
if (NAME1= Peter,[NAME2], 'Unknown')) asWorker0,
if(NAME1= Peter and NAME4= Adrian,Adrian,
if(NAME3= 'Charles',[NAME4],
if(NAME1= Peter and NAME3<> 'Charles' ,[NAME3],
if(NAME1<> Peter or NAME3<> 'Charles','Unknown')))) asWorker1,
if(NAME4= Adrian or NAME3= 'Charles',NAME5],
if(NAME1= Peter and NAME4<> Adrian,[NAME4],
if(NAME1<> Peter or NAME4<> Adrian or NAME3<> 'Charles','Unknown'))) asWorker2,
if(NAME4= Adrian or NAME3= 'Charles',NAME6],
if(NAME4<> Adrian or NAME3<> 'Charles',NAME5])) asWorker3,
if(NAME4= Adrian or NAME3= 'Charles',NAME7],
if(NAME4<> Adrian or NAME3<> 'Charles',NAME6])) asWorker4,
if(NAME4= Adrian or NAME3= 'Charles',[NAME8],
if(NAME4<> Adrian or NAME3<> 'Charles',NAME7])) asWorker5,
if(NAME4= Adrian or NAME3= 'Charles',NAME9],
if(NAME4<> Adrian or NAME3<> 'Charles',[NAME8])) asWorker6,
if(NAME4= Adrian or NAME3= 'Charles',NAME10],
if(NAME4<> Adrian or NAME3<> 'Charles',NAME9])) asWorker7,
if(NAME4= Adrian or NAME3= 'Charles',[NAME11],
if(NAME4<> Adrian or NAME3<> 'Charles',NAME10])) asWorker8,
if(NAME4= Adrian or NAME3= 'Charles',NAME12],
if(NAME4<> Adrian or NAME3<> 'Charles',[NAME11])) asWorker9,
if(NAME4= Adrian or NAME3= 'Charles',NAME13],
if(NAME4<> Adrian or NAME3<> 'Charles',NAME12])) asWorker10,
if(NAME4= Adrian or NAME3= 'Charles',[NAME14],
if(NAME4<> Adrian or NAME3<> 'Charles',NAME13])) asWorker11,
if(NAME4= Adrian or NAME3= 'Charles',NAME15],
if(NAME4<> Adrian or NAME3<> 'Charles',[NAME14])) asWorker12,
if(NAME4= Adrian or NAME3= 'Charles',NAME16],
if(NAME4<> Adrian or NAME3<> 'Charles',NAME15])) asWorker13,
if(NAME4<> Adrian or NAME3<> 'Charles',NAME16]) asWorker14
Resident Hierarchy6;
Drop
Table Hierarchy6;
STORE Hierarchy_JUL_2015
into
$(QVDPath) Hierarchy_JUL 2015.qvd;
Drop
Table Hierarchy_JUL_2015;
Please any help will be appreciated.
Thanks
Neetha
Where are you assigning values to these variables? vPMonth6 etc....and what values are assigned?
Hi Sunny,
Thanks for response.
Those variables are monthyear mentioned in the table name in resident load.
please solution for above script with loops if not possible.
maybe just looping over for current month data after creating hierarchy table,
so it loads resident table below with transformations,with out any issues.
To be noted that we never know how many levels will be generated with hierarchy function
for each month, in below script i have transformation for 16 levels and actual levels maybe
12 or 16.
Hope it is making sense?
Hierarchy_DEC_2016:
LOAD WORKER_ID,
[SUPERVISOR_ID],
NAME,
MonthYear,
recno,
PATH,
HIERARCHY,
if (NAME1= Peter,NAME1], 'Unknown') asWorker,
if(NAME1= Peter and NAME3= 'Charles','Charles',
if (NAME1= Peter,[NAME2], 'Unknown')) asWorker0,
if(NAME1= Peter and NAME4= Adrian,Adrian,
if(NAME3= 'Charles',[NAME4],
if(NAME1= Peter and NAME3<> 'Charles' ,[NAME3],
if(NAME1<> Peter or NAME3<> 'Charles','Unknown')))) asWorker1,
if(NAME4= Adrian or NAME3= 'Charles',NAME5],
if(NAME1= Peter and NAME4<> Adrian,[NAME4],
if(NAME1<> Peter or NAME4<> Adrian or NAME3<> 'Charles','Unknown'))) asWorker2,
if(NAME4= Adrian or NAME3= 'Charles',NAME6],
if(NAME4<> Adrian or NAME3<> 'Charles',NAME5])) asWorker3,
if(NAME4= Adrian or NAME3= 'Charles',NAME7],
if(NAME4<> Adrian or NAME3<> 'Charles',NAME6])) asWorker4,
if(NAME4= Adrian or NAME3= 'Charles',[NAME8],
if(NAME4<> Adrian or NAME3<> 'Charles',NAME7])) asWorker5,
if(NAME4= Adrian or NAME3= 'Charles',NAME9],
if(NAME4<> Adrian or NAME3<> 'Charles',[NAME8])) asWorker6,
if(NAME4= Adrian or NAME3= 'Charles',NAME10],
if(NAME4<> Adrian or NAME3<> 'Charles',NAME9])) asWorker7,
if(NAME4= Adrian or NAME3= 'Charles',[NAME11],
if(NAME4<> Adrian or NAME3<> 'Charles',NAME10])) asWorker8,
if(NAME4= Adrian or NAME3= 'Charles',NAME12],
if(NAME4<> Adrian or NAME3<> 'Charles',[NAME11])) asWorker9,
if(NAME4= Adrian or NAME3= 'Charles',NAME13],
if(NAME4<> Adrian or NAME3<> 'Charles',NAME12])) asWorker10,
if(NAME4= Adrian or NAME3= 'Charles',[NAME14],
if(NAME4<> Adrian or NAME3<> 'Charles',NAME13])) asWorker11,
if(NAME4= Adrian or NAME3= 'Charles',NAME15],
if(NAME4<> Adrian or NAME3<> 'Charles',[NAME14])) asWorker12,
if(NAME4= Adrian or NAME3= 'Charles',NAME16],
if(NAME4<> Adrian or NAME3<> 'Charles',NAME15])) asWorker13,
if(NAME4<> Adrian or NAME3<> 'Charles',NAME16]) asWorker14
Resident Hierarchy1;
Regard
Nitha
May be something like this.... Again I am not sure how you create your variable because you have shared no information, about them... but will make some assumptions
FOR i =1 to FieldValueCount('MonthYear')
LET vPMonth$(i) = FieldValue('MonthYear', $(i));
Hierarchy1:
Hierarchy(WORKER_ID,[SUPERVISOR_ID],NAME,,NAME,PATH,'/',HIERARCHY)
LOAD
DISTINCT WORKER_ID, [SUPERVISOR_ID],NAME,MonthYear,recno() as recno
Resident WorkerHierarchy
where MonthYear = '$(vPMonth1)';
[Hierarchy_$(vPMonth$(i))]:
LOAD WORKER_ID,
[SUPERVISOR_ID],
NAME,
MonthYear,
recno,
PATH,
HIERARCHY,
if ([NAME1] = Peter,[ NAME1], 'Unknown') asWorker,
if(NAME1= Peter and NAME3= 'Charles','Charles',
if (NAME1= Peter,[NAME2], 'Unknown')) asWorker0,
if(NAME1= Peter and NAME4= Adrian,Adrian,
if(NAME3= 'Charles',[NAME4],
if(NAME1= Peter and NAME3<> 'Charles' ,[NAME3],
if(NAME1<> Peter or NAME3<> 'Charles','Unknown')))) asWorker1,
if(NAME4= Adrian or NAME3= 'Charles',NAME5],
if(NAME1= Peter and NAME4<> Adrian,[NAME4],
if(NAME1<> Peter or NAME4<> Adrian or NAME3<> 'Charles','Unknown'))) asWorker2,
if(NAME4= Adrian or NAME3= 'Charles',NAME6],
if(NAME4<> Adrian or NAME3<> 'Charles',NAME5])) asWorker3,
if(NAME4= Adrian or NAME3= 'Charles',NAME7],
if(NAME4<> Adrian or NAME3<> 'Charles',NAME6])) asWorker4,
if(NAME4= Adrian or NAME3= 'Charles',[NAME8],
if(NAME4<> Adrian or NAME3<> 'Charles',NAME7])) asWorker5,
if(NAME4= Adrian or NAME3= 'Charles',NAME9],
if(NAME4<> Adrian or NAME3<> 'Charles',[NAME8])) asWorker6,
if(NAME4= Adrian or NAME3= 'Charles',NAME10],
if(NAME4<> Adrian or NAME3<> 'Charles',NAME9])) asWorker7,
if(NAME4= Adrian or NAME3= 'Charles',[NAME11],
if(NAME4<> Adrian or NAME3<> 'Charles',NAME10])) asWorker8,
if(NAME4= Adrian or NAME3= 'Charles',NAME12],
if(NAME4<> Adrian or NAME3<> 'Charles',[NAME11])) asWorker9,
if(NAME4= Adrian or NAME3= 'Charles',NAME13],
if(NAME4<> Adrian or NAME3<> 'Charles',NAME12])) asWorker10,
if(NAME4= Adrian or NAME3= 'Charles',[NAME14],
if(NAME4<> Adrian or NAME3<> 'Charles',NAME13])) asWorker11,
if(NAME4<> Adrian or NAME3<> 'Charles',[NAME14]) asWorker12
Resident Hierarchy1;
Drop Table Hierarchy1;
STORE [Hierarchy_$(vPMonth$(i))] into $(QVDPath) [Hierarchy_$(vPMonth$(i))].qvd (qvd);
NEXT;
I don't know if this will work or not because I didn't have some important details, but try it out. If it doesn't work, consider providing more details.
Best,
Sunny
Hi Sunny,
Sorry, my requirement is not clear.
for every month levels are created ,but level count varies from month to month.
when i execute my script it returns error field not found -<NAME12> .
hierarchy function creates 11 levels for current month ,previous month 12 levels.
levels count may go up or down depending on organization hierarchy structure
in a company.
so irrespective how many levels in a organization hierarchy for any particular month,
script should execute.
Hope it is clear now.
Thanks & Regards
Nitha