topic Do some functions in the script upon load in QlikView

Do some functions in the script upon load

Wed, 01 Feb 2012 15:19:15 GMT

I have previously loaded a few tables with a few variables. Now I want to create a new table with some additional variables:

Calculations:

load if(Number_number>=Bed_min and Bed_number<Bed_max and len(trim(Problem_in_room))=0 and not len(trim(TTimeStamp))=0 ,1,0) as Calc_StateOK,

TTimeStamp;

This does not produce the desired table, which is a table containing the calculated Boolean value and the TTimeStamp What's wrong? Do you have any ideas?

Do some functions in the script upon load

swuehl — Wed, 01 Feb 2012 15:24:59 GMT

Maybe this is just a copy&paste mistake, but you need to state the input table source, assumingly by using the RESIDENT keyword:

Calculations:

load

if(Number_number>=Bed_min and Bed_number<Bed_max and len(trim(Problem_in_room))=0 and not len(trim(TTimeStamp))=0 ,1,0) as Calc_StateOK,

TTimeStamp

resident YOURALREADYLOADEDTABLENAME;

Or maybe I have misunderstood your problem. Could you post your script, or best a small sample app? And what is the desired output table?

Re: Do some functions in the script upon load

Wed, 01 Feb 2012 16:08:39 GMT

Thank you for your answer swuehl!

The Problem is, that these variables used in the IF condition are all in different tables but all linked through a Room_id:

Table_Problems:

TTimestamp,

Room_id,

Problem_in_Room;

Table_Rooms:

Room_id,

Bed_min,

Bed_max;

Best,

Jonas

Do some functions in the script upon load

swuehl — Wed, 01 Feb 2012 16:16:16 GMT

You can't just call the field values from different tables like that. The functions will be evaluated record based using the input table stated by resident (or from ...) statement.

It is depending on what you want to achieve, there are probably some methods to achieve what you want.

For example, you can use lookup() function to access another table/field using a search field (could be Room_id).

Or you could join your two tables, then you have all fields in one input table and you can use the load script you posted above.

If you could post your complete script or best a small sample app (with attached excel to load or INLINE data, so a reload is possible), I think someone here might help you pretty fast.

Regards,

Stefan

Re: Do some functions in the script upon load

Wed, 01 Feb 2012 17:19:16 GMT

Thank you, here it comes: (it is also attached ...)

<code>

Table_Data:
Load * Inline [TTimeStamp,     Room_ID,    Bed_Number
02.10.2011,    1,    23
03.10.2011,    3,    5
03.10.2011,    4,    9
05.10.2011,    5,    14
06.10.2011,    6,    123
06.10.2011,    1,    31
06.10.2011,    1,    29
06.10.2011,    1,    21
06.10.2011,    2,    13
07.10.2011,    3,    6
08.10.2011,    1,    25
09.10.2011,    5,    14
10.10.2011,    4,    164
10.10.2011,    2,    20
11.10.2011,    1,    20
];

Table_Room:
Load * Inline [Room_id,    Bed_max,    Bed_min
1,    30,    10
2,    20,    10
3,    8,    3
4,    9,    5
5,    15,    11
6,    152,    92
];

Table_Problem:

Load * Inline [ Problem_Desc,     Problem_in_Room,     Room_ID
'Tab broken',                        1,    1
'Breakdown in TV',            1,    2
'Window broken',               1,    5
'Failed IT system',              1,    12
'Failed connection',           1,    1
'Guests destroyed bed',    1,    3

];

Calculations:

load

if(Number_number>=Bed_min and Bed_number<Bed_max and len(trim(Problem_in_room))=0,1,0) as Calc_StateOK,

TTimeStamp

resident YOURALREADYLOADEDTABLENAME;

Problem_Desc	Problem_in_Room	Room_ID
Tab broken	1	1
Breakdown in room	1	2
Arcing	1	5
Failed GPRS system	1	12
Failed fibreglass network	1	1
Guests destroy bed	1	3

</code>

Problem_Desc	Problem_in_Room	Room_ID
Tab broken	1	1
Breakdown in room	1	2
Arcing	1	5
Failed GPRS system	1	12
Failed fibreglass network	1	1
Guests destroy bed	1	3

What shall be my "YOURALREADYLOADEDTABLENAME"?

Cheers,

Jonas

Re: Do some functions in the script upon load

Wed, 01 Feb 2012 17:41:26 GMT

I could equally well do this in the expression for my charts, but in that case, the loading of the chart takes like 5 minutes, hence it would be easier to safe the expression as a field.

Best,

Jonas

Do some functions in the script upon load

swuehl — Wed, 01 Feb 2012 19:32:45 GMT

Jonas,

it's still a bit unclear to me how your data is related. Your Problem_Table only has Room_ID as link, but could show multiple Problems per Room_ID. In your Table_Data, you could have multiple entries for Room_ID, too, with different or same Bed_Number and Date. Your Calc_StateOK is checking some conditions, but for some TTimeStamps, there can be multiple, different results.

If you could do your evaluation in the front end, it would be good if you could add a chart table with your expression to the sample.

Regards,

Stefan

Do some functions in the script upon load

Thu, 02 Feb 2012 06:45:30 GMT

Dear Stefan,

Thank you for your very important remark. This is actually a crucial issue! But we have already found a solution for that. 🙂 - Hence, let's just focus on the question, how I can use values from different tables in an IF statement upon load.

Best,

Jonas