topic Re: Concatenate a Resident Load Table to Itself? in QlikView

Concatenate a Resident Load Table to Itself?

schneider2037 — Wed, 25 Nov 2020 16:16:04 GMT

Brand new to QV, and inherited a project. Trying to decipher the script yet, and have run across something I'm missing;

In the script editor, there are 20+ tabs with hundreds of lines of script. While I get most, ran into this one:

There is NO reference earlier in the script to create table "Boss_Units_TEMP", only here on a Resident load.

QUESTION: How/Why would I Concatenate into the same table a Resident Load is creating? Disregard the particulars of the fields please. Just trying to wrap my brain around the logic here.

Thanks all for any help/guidance. 28 days in to QV now...Jonathan

Concatenate(Boss_Units_TEMP)

Boss_Units_TEMP:

LOAD

KEY_StateDealer,

PLBT,

FQTR,

BOOKED_YEAR as FISCAL_YEAR,

BOOKED_YEAR &'-'& FQTR as FISCAL_KEY,

Sum(BOOKED_UNIT) as UNIT_BOOKED,

UNIT_FUEL_TYPE as FUEL_TYPE

Resident Boss

Group By KEY_StateDealer, UNIT_FUEL_TYPE, PLBT, FQTR, BOOKED_YEAR

;

DROP Table Boss;

Re: Concatenate a Resident Load Table to Itself?

tripatirao — Mon, 16 Apr 2018 12:47:50 GMT

Hi,

the above script is adding new fields to your table Boss_Units_TEMP table and in order to use aggr function(like sum,max, count) in the script you have to use group by clause.

hope it helps.

Re: Concatenate a Resident Load Table to Itself?

schneider2037 — Mon, 16 Apr 2018 12:56:37 GMT

Thanks Tripati - so it would be logical to see somewhere earlier in the script...a table that actually gets created called Boss_Units_TEMP? That's where I'm lost, as there is nothing that I see that creates that table other than right at this moment in the script.

Thanks!!! I very much appreciate your time on this.

Re: Concatenate a Resident Load Table to Itself?

stigchel — Mon, 16 Apr 2018 13:09:10 GMT

I think QV will produce a table not found error if the table Boss_Units_TEMP does not exist. So it should be there somewhere in the script. Please note that QV will name tables to the tablename (sql) or sheetname (xls) if not explcitely named in the scipt. Other options, hidden scripts? Any Include scripts?

Re: Concatenate a Resident Load Table to Itself?

marcus_sommer — Mon, 16 Apr 2018 13:11:00 GMT

To load resident from a table and merging the new load-results by concatenating or joining to the origin table isn't unusual for example if you need to join the max. Date from the first load for certain keys but you want to keep a different degree of granularity in this table as you calculate the Date or if you want to concatenate different granularities of certain measures maybe one on a transaction-level and the other on a monthly-level.

Assigning a tablename for a load which is concatenated to another table is not correct but it will be usually ignored but a concatenate-statement to a not existing table table should AFAIK result in an error.

Are you really sure that this table didn't exists? Maybe this table is generated within the hidden script, by an include-variable or it might be the result of a renaming (maybe by mapping-statement). Just add a exit script; before this load and take a look within the table-viewer.

If this table didn't exist it could be that a different table with the same table-structure exists to which this load is added whereby like above stated I would rather expect an error if not any ERRORMODE is implemented.

- Marcus

Re: Concatenate a Resident Load Table to Itself?

tripatirao — Mon, 16 Apr 2018 13:15:50 GMT

Yes,

Please check in your script the table Boss_Units_TEMP should be loaded previously.

Here some new fields are adding to BOSS table and finally the fields are concatenating to Boss_Units_TEMP.

Re: Concatenate a Resident Load Table to Itself?

Peter_Cammaert — Mon, 16 Apr 2018 13:23:56 GMT

Aha, this may be an interesting test case.

The QlikView Load prefix operations are executed after the LOAD itself. AFAIK they are never executed while performing the Load (like a Preceding Load is). But since the Load first creates the table to which it then tries to concatenate itself, this construct may actually work without errors.

The resulting table may have all records twice though. Can you check that ?

Or just comment out the Concatenate line and reload. Does it produce the same results in your document?

[Edit] Sorry, spelling errors. You know...

Re: Concatenate a Resident Load Table to Itself?

schneider2037 — Mon, 16 Apr 2018 13:31:43 GMT

Thanks! Hadn't thought of that angle. Will test. Right now, just tried to reload the whole shooting match and all sorts of errors flying around. This is a very buggy script I've inherited.

Will mark as closed/answered for now. Methinks I'm going to be spending tens of hours trying to debug from top to bottom first!

Thanks again,
Jonathan

Re: Concatenate a Resident Load Table to Itself?

Peter_Cammaert — Mon, 16 Apr 2018 13:33:15 GMT

No, that was just wishful thinking. It doesn't work like that, not in QV11.20 nor in 12.xx.

Your table Boss_Units_TEMP must exist before this script will run till the end. On condition that the script doesn't mess with ErrorMode...

Re: Concatenate a Resident Load Table to Itself?

schneider2037 — Mon, 16 Apr 2018 13:36:38 GMT

Drat. That actually made sense to me logically!

Will hunt around. As you (and other folks) indicate: it has to be there either explicitly or implicitly earlier in the script process.

Thanks!

Jonathan

Re: Concatenate a Resident Load Table to Itself?

schneider2037 — Mon, 16 Apr 2018 13:38:24 GMT

Thanks Marcus - will definitely take your advice and do "Exit Script;" and step through from beginning to end. The script itself is horrifyingly complex (literally approaching 100 different types of Loads) so there's definitely something amiss here.

Thanks!
Jonathan

Re: Concatenate a Resident Load Table to Itself?

marcus_sommer — Mon, 16 Apr 2018 13:53:50 GMT

It sounds that there are too many load-statements as useful within a single application - probably a generator which creates multiple qvd's and/or does multiple transformations. Very useful and quite common is to split the loadings into extracting, transformations, datamodel and report parts and also those into logically groups. This keeps the loadings readable, maintainable and increased often the performance.

- Marcus

Re: Concatenate a Resident Load Table to Itself?

schneider2037 — Mon, 16 Apr 2018 14:02:26 GMT

Marcus (and everyone) -

One nugget I forgot to mention is that this is the data model, not the application. The Application itself is a straight binary load over this data model qvw.

Apologies on not being very clear there.

Still doesn't lessen the complexity and horrific number of loads/resident loads, but at least this complexity is housed in the correct "layer."

~Jonathan