topic Load inline with variable values in QlikView

Load inline with variable values

Thu, 17 Mar 2011 10:14:12 GMT

Hi all,

I am facing the following challenge.

I have loaded a table, from Excel with the following fields

ID, key, x, y

I prepared, on the script, the following table

// Sample DynamicInline
DynamicInline:
LOAD * INLINE [
 key, Field1, Field2
 1, 1, 3
 1, 2, 2
 1, 3, 1
 1, x, y

 2, 4, 6
 2, 5, 5
 2, 6, 4
 2, x, y

 3, 7, 9
 3, 8 ,8
 3, 9, 7
 3, x, y

 ....
];

Now I need the following...
when I select on the table loaded from excel, I need x and y (in DynamicInline table)
to acquire the same value in the selection row.

Any Idea????
Thank you

Load inline with variable values

john_duffy — Thu, 17 Mar 2011 17:15:01 GMT

Hello.

I need a bit more information. You have two tables - one from Excel and one created using inline load. Currently, based on the field names, the two tables are linked on one field - key. Are you saying that if you select key, you only want the row with the Field1 value 'x' and the Field2 value 'y' from the inline load table?

Could you attach a sample application using an inline load for both tables.

Thanks.

Load inline with variable values

Thu, 17 Mar 2011 19:00:01 GMT

Do you think it is possible?

Load inline with variable values

Thu, 17 Mar 2011 19:36:27 GMT

Hi, I try to better explain... and I attach a sample...


SET ThousandSep='.';
SET DecimalSep=',';
SET MoneyThousandSep='.';
SET MoneyDecimalSep=',';
SET MoneyFormat='€ #.##0,00;-€ #.##0,00';
SET TimeFormat='hh:mm:ss';
SET DateFormat='DD/MM/YYYY';
SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff]';
SET MonthNames='gen;feb;mar;apr;mag;giu;lug;ago;set;ott;nov;dic';
SET DayNames='lun;mar;mer;gio;ven;sab;dom';

Table1:
LOAD * INLINE [
 ID, key, x, y
 1, 1, 5, 22
 2, 1, 100, 3000
 3, 2, 24, 24
 4, 2, 400, 2000
 5, 3, 36, 37
 6, 3, 700, 8500
];


Table2:
LOAD * INLINE [
 key, Field1, Field2
 1, 1, 30
 1, x+y, y-x
 2, 4, 60
 2, x+y, y-x
 3, 7, 90
 3, x+y, y-x
];

I try to attach some screenshot for what I'd like to happen:

Selecting none:

Selecting ID=1

Selecting ID=2

Selecting ID=3

Selecting ID=4

Selecting ID=5

Selecting ID=6

I do not know if this is possible... but consider this is only a sample...

Thanks a lot for your help.

Load inline with variable values

john_duffy — Thu, 17 Mar 2011 19:44:10 GMT

Hello.

I have attached a sample application. Let me know if this solves your issue.

Thanks,

John.

Load inline with variable values

Thu, 17 Mar 2011 19:53:23 GMT

Oh my God... It is what I was looking for... could you please try to explain how you did it? I though I had to write something on script... but script remains the same...

Could you help me to understand? I'm new with QlikView. Thank you very much

Load inline with variable values

john_duffy — Thu, 17 Mar 2011 20:05:39 GMT

I used two expressions - one to calculate using Field1 and one to calculate using Field2. They are basically the same.

The Field1 expression is : if(getselectedcount(ID) = 0,Field1,if(Field1 = 'x+y',x + y,Field1))

The getselectedcount function allows me to see if the user has selected an ID. If none are selected simply display Field1.

If one or more IDs are selected and the value in Field1 = 'x+y', sum fields x and y. If one or more IDs are selected and the value in Field1 is not 'x+y', display Field1.

Also, in order to only display key, the expression Field1 and the expression Field2, I have hidden dimensions Field1 and Field2. This is done selecting Hide Column in the chart's Presentation tab.

Glad this helped.

John.

Load inline with variable values

Thu, 17 Mar 2011 20:12:14 GMT

Thanks thanks thanks... I know I have so much to learn 🙂

I go OT but I have another question... how to do to attach files to post?

Load inline with variable values

john_duffy — Thu, 17 Mar 2011 20:17:33 GMT

In the post there are 3 tabs - Compose, Options and Preview.

Go to the Options tab and under File attachment select Add/Update.

Load inline with variable values

Thu, 17 Mar 2011 20:22:00 GMT

🙂

I hope I will be able, in the future, to help you somehow.