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

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

Mon, 10 Jun 2013 15:32:54 GMT

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_id	system	phase
1	1	clean procedure
2	3	clean procedure
3	1	clean procedure
4	4	clean procedure
5	2	clean procedure
6	2	clean procedure
7	3	clean procedure
8	3	clean procedure
9	4	cooking procedure
10	2	clean procedure
11	3	eat procedure
12	1	clean procedure
13	1	clean procedure
14	2	cooking procedure
15	2	clean procedure
16	4	clean procedure
17	1	cooking 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;

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;

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!

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

Gysbert_Wassenaar — Mon, 10 Jun 2013 15:39:53 GMT

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;

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

Mon, 10 Jun 2013 15:49:34 GMT

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;

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

Gysbert_Wassenaar — Mon, 10 Jun 2013 15:57:54 GMT

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.

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

Tue, 11 Jun 2013 08:27:55 GMT

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?