Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
Highlighted
meyham2001
New 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

Re: Subfield parts via variable in the loop function

May be like attached

View solution in original post

15 Replies
meyham2001
New Contributor

Re: Subfield parts via variable in the loop function

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.

Re: Subfield parts via variable in the loop function

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.

MVP & Luminary
MVP & Luminary

Re: Subfield parts via variable in the loop function

In what table does the field FilteredFields reside?

-Rob

flipside
Valued Contributor II

Re: Subfield parts via variable in the loop function

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
New Contributor

Re: Subfield parts via variable in the loop function

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
New Contributor

Re: Subfield parts via variable in the loop function

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


  • DomainFilteredFields
  • DatasetFilteredFields
  • ...
flipside
Valued Contributor II

Re: Subfield parts via variable in the loop function

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
New Contributor

Re: Subfield parts via variable in the loop function

Here's an example of the problem.

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

Re: Subfield parts via variable in the loop function

Why not use a Mapping load here?

What is a Mapping Load in Qlikview