topic Re: question on using a for loop structure to create fields in QlikView

question on using a for loop structure to create fields

gerrycastellino — Fri, 06 Feb 2015 18:25:29 GMT

I'm using the Hierarchy function to create a hierarchy tree structure.

I want to fix the node depth to 15. I'm using the below for loop to do this, but does not work.

The leaf nodes get generated as Location1, Location2, etc.

My logic is as follows for my loop:

Loop through my fields, say if the leaf ends at 7, use the for loop to generate fields [8-15]

I seem to be having a problem with this statement:

If FieldNumber('Location'$(v_level),’TMP_FRY14A’) = 0

It does not like the concatenation that I've used.

Any help appreciated !

[TMP_FRY14A]:

Hierarchy(ChildID, ParentID, Location, ParentLocation, Location, Path, \, NodeDepth)

load

NODE_ID as ChildID,

PARENT_ID as ParentID,

NAME as Location,

IS_LEAF,

PRIORITY,

IS_DEFAULT,

HIERARCHY_CATEGORY,

HIERARCHY_NAME

FROM [$(Parameters_Path_Qvd_Data)\FRY14A.qvd](qvd);

For v_level1 = 1 to 15

If FieldNumber('Location'$(v_level),’TMP_FRY14A’) = 0 then

For v_level2 = $v_level1 to 15

concatenate (TMP_FRY14A)

Load

null() as Location$(v_level2)

autogenerate(1) where RecNo() <0;

Next

Re: question on using a for loop structure to create fields

rwunderlich — Fri, 06 Feb 2015 18:38:12 GMT

Could you be more specific about "It does not like the concatenation that I've used.". Script error? Unexpected results?

What are you trying to do with?

autogenerate(1) where RecNo() <0;

That will generate zero rows Same as

Autogenerate 0;

-Rob

Re: question on using a for loop structure to create fields

gerrycastellino — Fri, 06 Feb 2015 21:03:12 GMT

I'm getting the foll. error in the log:

2015/02/06 10:28:41: 0037 If FieldNumber('Location',’TMP_FRY14A’) = 0 then

2015/02/06 10:28:41: General Script Error

2015/02/06 10:28:41: Execution Failed

2015/02/06 10:28:41: Execution finished.

What I'd like the construct to do is test for the existence of a field via my v_level expansion loop.

In effect I'd like the line to read like:

**** If FieldNumber('Location7',’TMP_FRY14A’) = 0 then ****

autogenerate(1) where RecNo() <0;

I just wanted to create blank columns in my table, I came across the code above.

It works when I do it as follows:

elseif FieldNumber('Location7','TMP_FRY14A') = 0 then

concatenate (TMP_FRY14A)

Load

null() as Location7,

null() as Location8,

null() as Location9,

null() as Location10,

null() as Location11,

null() as Location12,

null() as Location13,

null() as Location14,

null() as Location15

autogenerate(1) where RecNo() <0;

Re: question on using a for loop structure to create fields

rwunderlich — Fri, 06 Feb 2015 23:27:16 GMT

I think the general script error is because you have backticks, instead of single quotes, around your table name.

I think the loop can be simplified to:

For v_level = 1 to 15

If FieldNumber('Location$(v_level)','TMP_FRY14A') = 0 then

concatenate (TMP_FRY14A)

Load null() as Location$(v_level)

autogenerate 0;

ENDIF

-Rob

http://masterssummit.com

http://robwunderlich.com