Skip to main content

Qlik

cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Not applicable
‎2017-01-28 03:54 AM

script for loop or do while loop

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

863 Views
0 Likes
4 Replies
sunny_talwar
MVP
MVP
‎2017-01-28 07:19 AM

Where are you assigning values to these variables? vPMonth6 etc....and what values are assigned?

547 Views
0 Likes
Not applicable
‎2017-01-28 12:48 PM
Author

In response to sunny_talwar

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


547 Views
0 Likes
sunny_talwar
MVP
MVP
‎2017-01-28 04:21 PM

In response to

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

547 Views
0 Likes
Not applicable
‎2017-01-29 04:03 AM
Author

In response to sunny_talwar

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

547 Views
0 Likes