Skip to main content
Announcements
Save $600 on Qlik Connect registration! Sign up by Dec. 6 to get an extra $100 off with code CYBERSAVE: REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
meyham2001
Contributor
Contributor

Subfield parts via variable in the loop function

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.

1 Solution

Accepted Solutions
sunny_talwar

May be like attached

View solution in original post

15 Replies
meyham2001
Contributor
Contributor
Author

It looks like vRole is not getting any values because the fields that are left joined are named:

  • Domain, Dataset...
  • DomainName, DatasetName...
  • DomainEmail, DatasetEmail...
  • DomainShortName, DatasetShortName...

And all entities get the same resault.

sunny_talwar

It might be helpful if you can share a sample to look at this. Difficult to see what the issue might be without getting to run the script. On you own, you can try to run this in debug mode and/or add trace statement to see the variables step by step.

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

In what table does the field FilteredFields reside?

-Rob

flipside
Partner - Specialist II
Partner - Specialist II

Aren't you resetting vIndex to 1 on each vTab loop? Does it need to be above the 'inner' Next command for vRole?

flipside

meyham2001
Contributor
Contributor
Author

Yes, I saw it imediately but still, when I changed the code it works the same way.

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;

    vIndex = $(vIndex)+1;

    Next;

Next;

meyham2001
Contributor
Contributor
Author

FilteredFields is residing within each entity and it gets a name approriately:


  • DomainFilteredFields
  • DatasetFilteredFields
  • ...
flipside
Partner - Specialist II
Partner - Specialist II

It might need you to upload an example. How exactly are you storing the possible fields you reference as FilteredFields? If you are putting them into separate variables you could call them in this way ...

Set DomainFilteredFields  = 'DomainAccountable,DomainOwner,DomainResponsible';
Set DatasetFilteredFields = 'DatasetAccountable,DatasetControl,DatasetInformed,DatasetResponsible,DatasetSupport';
Set ProcessFilteredFields = 'ProcessAccountable,ProcessControl,ProcessInformed,ProcessOwner,ProcessResponsible';


For Each vTab in 'Domain', 'Dataset', 'Process'
vIndex = 1;
For Each vRole in Subfield($(vTab)FilteredFields, ',', $(vIndex))

... but you've mentioned fields.

meyham2001
Contributor
Contributor
Author

Here's an example of the problem.

Domain and Dataset contain ID-s of the corresponding RACI people.

sunny_talwar

Why not use a Mapping load here?

What is a Mapping Load in Qlikview