Subfield parts via variable in the loop function

[meyham2001]

Dear community,

I have a following problem:

For Each vTab in 'Domain', 'Dataset', 'Fields', 'Files', 'Process'

    vIndex = 1;

    For Each vRole in Subfield(FilteredFields, ',', $(vIndex))

        Left Join (DG_$(vTab))

    LOAD

        %RaciTpID        AS $(vRole),

        RaciName         AS $(vRole)Name,

        RaciEmail        AS $(vRole)Email,

        RaciShortName    AS $(vRole)ShortName

    Resident DG_RACI_temp;

    Next;

    vIndex = $(vIndex)+1;

Next;

Now, the point is: I have entities that go in vTab variable and each entity can have one to all of the Roles (Responsible, Accountable, Owner...). RACI table is the lookup table that contains the list of people and any of these people can have one to all roles in one to all entities. I Concatinated all possible fields for all entities in a fields called Domain/Dataset/...FilteredFields. They look like this:

DomainFilteredFields = 'DomainAccountable,DomainOwner,DomainResponsible'

DatasetFilteredFields = 'DatasetAccountable,DatasetControl,DatasetInformed,DatasetResponsible,DatasetSupport'

ProcessFilteredFields = 'ProcessAccountable,ProcessControl,ProcessInformed,ProcessOwner,ProcessResponsible'

...

Now I want to left join the data (Name, Email, Short name) to Domain/Dataset... for these people from RACI table.

I'm not getting any errors and it looks like the loop just goes through.

Any suggestions on what I could be doing wrong?

Thank you in advance.

[sunny_talwar]

I guess you want to shorten the script.... Is that why you using a For Loop?

[flipside]

As Sunny T has suggested, a mapping load is probably easier to implement and manage. However, could it also just be a case of creating your variables with a peek function after each table, eg ...

Let DomainFilteredFields  = peek('DomainFilteredFields');

and then call the variable with a prefix in your subfield ...

For Each vRole in Subfield($(vTab)FilteredFields, ',', $(vIndex))

I think that's what you're trying to achieve.

flipside

[meyham2001]

I'm using it because there can be more and more entities and they don't necesserily have all the roles. I'm not sure Mapping load can do it. And beside that, now I really want to know why this approach doesn't work when, I think, it should.

[sunny_talwar · Accepted solution]

May be like attached

[rwunderlich]

The qvw you posted works for me -- that is, the Names are getting populated. Am I missing the objective? BTW, the QVW script doesn't match the script you posted. The posting is missing the variable in the FilteredFields reference which threw me off.

What it the final result you are trying to achieve?  A simple solution may be to just crosstable load the data and link it to the user table like this:

CrossTable  (DomainRole, %RaciTpID)

See attached example. If you want to single table, or you want the "Domain/Dataset" bit stripped off that's a simple addition. Let us know.

-Rob

http://masterssummit.com

http://qlikviewcookbook.com

[meyham2001]

Great. Thank you all for the help. I know there are probably many ways how to work this through but I was wondering why my approach wasn't working.