Reload Stalls in Script with Two-Table Join

[raynac]

Hoping someone can see what I am doing wrong; I have done this many times before and never had an issue.

I have two tables and I am trying to load two fields from one table into the LOAD statement of the other.  The script reloads fine as long as the two fields are not accessed in the preceding load statement, but the minute that I add them back into the LOAD, the reload stalls right at the very end.

Am I missing something?

To show an example...this loads fine:

LOAD

	"reservation_number"  as "Room Night ResNum",
	"number_of_adults" as "Room Night numAD",
	"number_of_xadult" as "Room Night numXAD",
	"number_of_senior" as "Room Night numSEN",
	"number_of_infant" as "Room Night numINF",
	"number_of_children" as "Room Night numCHI",
	"number_of_youth" as "Room Night numYOU",
	"number_of_other" as "Room Night numOT"//,
//	meal_plan as "Room Night Meal Plan"//,
//	description as "Room Night Rate Type Description"
	;

SQL SELECT rr.*, rt.meal_plan, rt.description

FROM $(TBLPRE)fdresrev rr inner join $(TBLPRE)fdratetype rt on

rr.property_code = rt.property_code

and rr.rate_category = rt.rate_category

and rr.rate_type = rt.rate_type

where sba_date>=from_date

and sba_date<=to_date;

But this stalls:

LOAD

	"reservation_number"  as "Room Night ResNum",
	"number_of_adults" as "Room Night numAD",
	"number_of_xadult" as "Room Night numXAD",
	"number_of_senior" as "Room Night numSEN",
	"number_of_infant" as "Room Night numINF",
	"number_of_children" as "Room Night numCHI",
	"number_of_youth" as "Room Night numYOU",
	"number_of_other" as "Room Night numOT",
	meal_plan as "Room Night Meal Plan",
	description as "Room Night Rate Type Description"
	;

SQL SELECT rr.*, rt.meal_plan, rt.description

FROM $(TBLPRE)fdresrev rr inner join $(TBLPRE)fdratetype rt on

rr.property_code = rt.property_code

and rr.rate_category = rt.rate_category

and rr.rate_type = rt.rate_type

where sba_date>=from_date

and sba_date<=to_date;

(The meal_plan and description fields are the ones from the other table, obviously.  And I have done this in other views successfully!)

Thanks for any suggestions anyone can offer!

[swuehl]

This LOAD statement stalls when used as the only LOAD statement in your script or are there any other LOAD statements that may also create fields "Room Night Meal Plan" and "RoomNight Rate Type Description"?

I mean, could there be a linking of two tables in your QV data model by more than one common field taking place?

Have you tried using the debug dialog in script editor, reloading with limiting the data to 10 records? And then look at the resulting data model? Do you see any synthetic keys and tables?

[Anonymous]

Just to be safe, can you try a quick load where you put meal_plan and description within double-quotes like the other fields in the preceding loads? I'd hate to spin wheels if it's something as simple as case sensitivity on the result-set column name.

Otherwise confirm that "Room Night Meal Plan" & "Room Night Rate Type Description" aren't already used as field names within your logical model. As swuehl pointed out above, that would cause you to see a pause at the end of the load while QV tries to do joins and build synthetic keys.

Good luck!

[raynac]

I absolutely see what you are asking.  There are other LOAD statements but those field names are definitely unique.

I am not familiar with the debug. I tried it and cannot get it to work...but I will research, and see if I can figure it out.

Thank you!

[Anonymous]

If you're uncomfortable in the debugger, you can put "First 10" in front of your load statement.

Limited Load and the First Prefix

[swuehl]

I am not familiar with the debug. I tried it and cannot get it to work...but I will research, and see if I can figure it out.

That should be just like

a) open script editor CTRL-E

b) open Debug dialog (button in menu bar)

c) check 'limited Load' option (10 default)

d) click on Run

[raynac]

I did that, but either

1) I get a few seconds in and get a few dozen of these: "Connection is busy with results for another hstmt" or

2) it stops at the end and I have to crash out of the program.  I am guessing it's not supposed to do that either?

[swuehl]

Hm, try stepping through the statements in debug dialog (instead of d) Run, click on Step, one time for each statement).

When running the SQL statements,  wait small amount of time inbetween.

Which DBMS are you running your queries against?

[raynac]

I did try this but got the same result.

I am thoroughly embarrassed to admit that I figured this out.  There is a second LOAD underneath that, essentially, concatenates to this one, and I didn't remember that (these are new additions to our database and to Qlikview).  So I didn't add the two extra fields to it.  Once I noticed this and added them, it worked great. 

I very much appreciate your help.

[Anonymous]

Awesome...glad you got it