topic Re: Subfield parts via variable in the loop function in QlikView

Subfield parts via variable in the loop function

meyham2001 — Mon, 23 Jan 2017 11:22:43 GMT

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.

Re: Subfield parts via variable in the loop function

meyham2001 — Mon, 23 Jan 2017 11:46:04 GMT

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

sunny_talwar — Mon, 23 Jan 2017 12:25:20 GMT

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.

Re: Subfield parts via variable in the loop function

rwunderlich — Mon, 23 Jan 2017 13:00:51 GMT

In what table does the field FilteredFields reside?

-Rob

Re: Subfield parts via variable in the loop function

flipside — Mon, 23 Jan 2017 13:05:13 GMT

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

flipside

Re: Subfield parts via variable in the loop function

meyham2001 — Mon, 23 Jan 2017 13:45:29 GMT

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;

Re: Subfield parts via variable in the loop function

meyham2001 — Mon, 23 Jan 2017 13:47:07 GMT

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

DomainFilteredFields
DatasetFilteredFields
...

Re: Subfield parts via variable in the loop function

flipside — Mon, 23 Jan 2017 14:22:48 GMT

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.

Re: Subfield parts via variable in the loop function

meyham2001 — Mon, 23 Jan 2017 14:54:33 GMT

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

sunny_talwar — Mon, 23 Jan 2017 15:03:37 GMT

Why not use a Mapping load here?

What is a Mapping Load in Qlikview

Re: Subfield parts via variable in the loop function

sunny_talwar — Mon, 23 Jan 2017 15:05:07 GMT

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

Re: Subfield parts via variable in the loop function

flipside — Mon, 23 Jan 2017 15:14:37 GMT

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

Re: Subfield parts via variable in the loop function

meyham2001 — Mon, 23 Jan 2017 15:32:57 GMT

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.

Re: Subfield parts via variable in the loop function

sunny_talwar — Mon, 23 Jan 2017 15:42:42 GMT

May be like attached

Re: Subfield parts via variable in the loop function

rwunderlich — Mon, 23 Jan 2017 18:10:07 GMT

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

Re: Subfield parts via variable in the loop function

meyham2001 — Mon, 23 Jan 2017 18:23:59 GMT

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.