Skip to main content
Woohoo! Qlik Community has won “Best in Class Community” in the 2024 Khoros Kudos awards!
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.

15 Replies
sunny_talwar

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

flipside
Partner - Specialist II
Partner - Specialist II

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

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

May be like attached

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

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

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.