Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Highlighted
Not applicable

for each loop and array in load statement - How to handle text with space?

Hi,

i try to load data with a for each - loop and an array.

When i use numbers the syntax works.

But i have to use text with space, for example "cooking procedure".

A little example table:)

DataTable:

Action_idsystemphase
11clean procedure
23clean procedure
31clean procedure
44clean procedure
52clean procedure
62clean procedure
73clean procedure
83clean procedure
94cooking procedure
102clean procedure
113eat procedure
121clean procedure
131clean procedure
142cooking procedure
152clean procedure
164clean procedure
171cooking procedure

This is the Syntax i use in load statement:

SET sysArray = '1','2','3','4';

SET phaseArray='cooking procedure','clean procedure','eat procedure';

SET id=0;

for each i in $(phaseArray)

     for each j in $(sysArray)

          ActionDone:

          Load $(id) as ID,count(Action_id) as AmountActions

           resident DataTable where system=$(j) and phase =$(i);

          SET id=$(id)+1;

     next

next

But QV is not able to get the first field of phaseArray.

I get following error:

ERROR:   Field not found - <cooking>

               ActionDone:

                                   Load 0 as ID,count(Action_id) as AmountActions resident +

                                   DataTable where system=cooking procedure and phase =1;

I also tried:

SET sysArray = '1','2','3','4';

SET phaseArray=''cooking procedure'',''clean procedure'',''eat procedure'';                           

            // i add to the first  '  another  ', dont read it as "

SET id=0;

for each i in $(phaseArray)

     for each j in $(sysArray)

          ActionDone:

          Load $(id) as ID,count(Action_id) as AmountActions resident DataTable

          where system=$(j) and phase =$(i);

          SET id=$(id)+1;

     next

next

ERROR:          Script line errror:

                       for each i in ''cooking procedure'',''clean procedure'',''eat procedure''

It seems like that the space is the Problem.

I cant change these identifiers.

Anybody any idea?:)

Thx for help!

1 Solution

Accepted Solutions
MVP & Luminary
MVP & Luminary

Re: for each loop and array in load statement - How to handle text with space?

Try where system=$(j) and phase = '$(i)';

But what's wrong with just using a load statement to calculate the counts?

ActionDone:

Load autonumber(system&phase) as ID, count(Action_id) as AmountActions

resident DataTable

group by system, phase;



talk is cheap, supply exceeds demand
4 Replies
MVP & Luminary
MVP & Luminary

Re: for each loop and array in load statement - How to handle text with space?

Try where system=$(j) and phase = '$(i)';

But what's wrong with just using a load statement to calculate the counts?

ActionDone:

Load autonumber(system&phase) as ID, count(Action_id) as AmountActions

resident DataTable

group by system, phase;



talk is cheap, supply exceeds demand
Not applicable

Re: for each loop and array in load statement - How to handle text with space?

Hi,

nothing wrong with that:D

works wonderful^^

Than i have another question, does the function autonumber(system&phase) always creates the same id?

So can i use it as follows for the second table

ActionDone:

Load autonumber(system&phase) as ID, count(Action_id) as AmountActions

resident DataTable

group by system, phase;

IDsPerAction:

Load autonumber(system&phase) as ID, Action_id resident DataTable;

?

MVP & Luminary
MVP & Luminary

Re: for each loop and array in load statement - How to handle text with space?

The autonumber function always returns the same number for the same input during the same reload. It's not guaranteed to return the same number across different reloads. That depends on the order in which the data is loaded. If that order changes then the numbers can change too.


talk is cheap, supply exceeds demand
Not applicable

Re: for each loop and array in load statement - How to handle text with space?

Hi,

thx for the great help.

I tried

system=$(j) and phase = '$(i)';

and this works:)

But i'm not sure if i understand the autonumber-function, wich is very interesting too.

Do u mean it wont work if i do a partial reload?

But if i make sure that they will always be reloaded in this order, it will work?