topic loosen table script in QlikView

loosen table script

Mon, 07 Mar 2011 13:14:04 GMT

Hi community!!

I´m studing some doubts I have and its apllications. Currently, I have a problem with qlikview reference manual´s loosen table statement example:

I mix exampple on page 160 with statement explined on page 326, but something is wrong and I do´t know what what it should be. Here you are my
senteces on the script.

LOAD YEAR,
DATE
FROM (biff, embedded labels, table is Hoja1$);

LOAD DATE,
PROD,
AMOUNT
FROM (biff, embedded labels, table is Hoja2$);

LOAD PROD,
PRODGRP
FROM (biff, embedded labels, table is Hoja3$);

Hoja2$:
SELECT *
FROM (biff, embedded labels, table is Hoja2$);
LOOSEN TABLE Hoja2$;

I hope someone could help me.

Thanks, Fiber9906

loosen table script

Mon, 07 Mar 2011 17:19:40 GMT

Hi,

I am reviewing doubts and I always have same problem. Every example on the tutorial are like

select * from table3

but I create my examples on excel, so my table is (biff, embedded labels, table is sheet2$);

I said that because if I load by inline statement everything is fine, but all documents I´ve got are excel, access queries or txt.

Could anyone help me?

Thanks! Fiber 9906 ; )

loosen table script

tresB — Mon, 07 Mar 2011 17:46:57 GMT

Hi, your issue is not clear to me. when you load using INLINE, there is no issue, but when loading from excel then there is probably an issue. what is that? Regards, tresesco

loosen table script

Tue, 08 Mar 2011 00:15:15 GMT

You are right, I mixed two different problems. I apologize. Nevertheless, I upload a file with my script problem. Can you help me?

Thanks, Fiber9906

loosen table script

tresB — Tue, 08 Mar 2011 05:52:21 GMT

Instead of SELECT use LOAD in the commented part.when you use SELECT QV expects an ODBC-type connection. as you are fetching the data directly from excel with no ODBC connection, go simply with LOAD command.

Hope this helps.

Regards, tresesco

loosen table script

Tue, 08 Mar 2011 08:24:50 GMT

Hi tresesco,

Thank you for answer me. I´d tried this before, but pivot table result isn´t right, beacuse every prodgrp sum(amount)=108. I must be mistaked in other thing. What it should be?

Thanks, Fiber9906

loosen table script

tresB — Tue, 08 Mar 2011 12:24:54 GMT

Hi,

i will have to go through your application again(can't do it now). and yes i don't know the values of your excel. can you just upload the excels as well?

Regards, tresesco

loosen table script

Tue, 08 Mar 2011 13:15:39 GMT

Hi don´t worry if you can´t do it know. I attach you excel file.

Thanks!!

loosen table script

Wed, 09 Mar 2011 16:06:37 GMT

Hi to all!!

I remember that post. Anyone can help me?

Thanks. Fiber9906

AW:Re: loosen table script

Wed, 09 Mar 2011 16:16:33 GMT

Hello Fiber,

use LOAD instead of SELECT. Thats all. As tresesco mentioned above "Select" is for SQL-Statements.

Regards, Roland

AW:Re: loosen table script

Wed, 09 Mar 2011 18:00:13 GMT

Hi Roland and thanks for you answer, but something is wrong in my code because if I use LOAD instead of SELECT, the result is:

YEAR	PRODGRP	SUM(AMOUNT)
1999	X	108
	Y	108
	Z	108
	Total	324
2000	X	108
	Y	108
	Z	108
	Total	324
Total		648

And this is a wrong result. Could you modified script on the attached document and send it? I think it will be the only way to understand what I am wrong.

Thanks for your help, Fiber9906

loosen table script

tresB — Wed, 09 Mar 2011 19:02:11 GMT

but i am getting different output with your excel data ! please check the attached application. and yes, the data is doubled because the 2nd file is loaded twice and so they are concatenated (2nd one to the 1st one).

if this does not clear your doubt, please tell where exactly you are having doubts, what you expect the output to come. is it something, your doubts are with LOOSEN TABLE?

Regards, tresesco

loosen table script

Thu, 10 Mar 2011 01:04:26 GMT

Hi (again) Tresesco,

I´ve understood write LOAD instead of SELECT statement and I´ve also understood the data is doubled. That´s ok.

It doesn´t matter (because is an example) if numeric data are diferent, my only problem is that I´m trying to repeat an exercise of Reference Manual (page 161) to know which are tables behaviour and how to do the script.

After I´ve done all things you said to me, I still have no idea why it doesn´t work. I think this is a very simple exercise... but not for me (jajajaja)

I appreciate any idea, Fiber9906

PS. I attach manual´s images and look the way to declare LOOSEN TABLE. You call first Tab1: select * from Trans; loosen table Tab1;

Which is table´s name? Tab1 or Trans?

loosen table script

Oleg_Troyansky — Thu, 10 Mar 2011 01:53:50 GMT

Fiber9906,

I'm not sure what you are trying to accomplish, but let me tell you that "Loosely coupled tables" is a very shaky ground and is very rarely the best solution of most common problems. In most cases, they add to confusion and increase problems instead of solving them.

Unless you know well what you are doing, I'd recommend to stay away from loosely coupled tables.

cheers,

AW:Re: loosen table script

Thu, 10 Mar 2011 17:19:05 GMT

Hi fiber,

attached find your application with two textboxes containing an image of tablestructure. The usual case is linking tables with same fieldnames (left image). This is a very common features of QV and I like it very much. If you don't want to link tables with same fieldnames, rename the fields or qualify the tables.

Sometimes you don't want to link two table, but keep the fieldnames. Then you can loosen these tables. But as Oleg mentioned above this is a very unusual situation in QV and I personally never had this before. So it is more a theoretical think to know but not to use.

Note: During testing I had some troubles with relinking the tables after recomment the loose-statement. Seems to be a bug: to relink successfully you have to change the loading order of the tables.

AW:Re: loosen table script

Thu, 10 Mar 2011 19:28:23 GMT

Thank you both (Oleg and Roland) for spending your time helping me. I am posting since august and you solve me a lot of problems, so I just want to study by myself the manual to increase my knowlegde about qlikview and try to get things more complicated.

When I read that part of manual, it seems to me very interesting but I couldn´t do it by myself. After your answers, is good to know that you never used it. I forget LOOSEN TABLE.

I am really appreciated, Fiber9906