topic Subrutines in a LOAD in QlikView

Subrutines in a LOAD

Sat, 23 May 2009 05:25:14 GMT

Hello, is it possible to use a subrutine or sub-loading from a QVD. As example here's my script that is failing:

HNUMC5:

LOAD

"HnuD01_01A", "HnuD02_01M", "HnuD04_01E", "HnuD05_01S", // OK

(HnuD01_01A * 100 + HnuD02_01M) as Periodo, // OK

IF (HnuD10_01A=1405 or HnuD10_01A=1406,HNUMC5SMN * (-1), 0) as C_MoraContable, // OK

IsNull((Select Sum(b.HnuMC1SMN * -1) From HNUMC1 b Where a.HnuD01_01A = b.HnuD01_01A And a.HnuD02_01M = b.HnuD02_01M And a.HnuD04_01E = b.HnuD04_01E And a.HnuD05_01S = b.HnuD05_01S And a.HnuD06_01M = b.HnuD06_01M And a.HnuD08_01R = b.HnuD08_01R And a.HnuD13_01M = b.HnuD13_01M And a.HnuD15_01M = b.HnuD15_01M And a.HnuD17_01E = b.HnuD17_01E And a.HnuD1CId = b.HnuD1CId And a.HnuD25_01T = b.HnuD25_01T And a.HnuD26_01C = b.HnuD26_01C And a.HnuD27_01D = b.HnuD27_01D And a.HnuD28_01G = b.HnuD28_01G And a.HnuD29_01T = b.HnuD29_01T And a.HnuD39_01E = b.HnuD39_01E And a.HnuD40_01E = b.HnuD40_01E And a.HnuD44_01S = b.HnuD44_01S And a.HnuD45_01T = b.HnuD45_01T And a.HnuD60_01S = b.HnuD60_01S And a.HnuD61_01F = b.HnuD61_01F And a.HnuD67_01C = b.HnuD67_01C And b.HnuD25_01T in (3,4,5,6)),0) As C_PromPatrimonio // FAIL

FROM [HNUMC5_.qvd] (qvd) a;

Think it fails because Qlikview detects the 'where' located i the 'Isnull' argument and the 'FROM [HNUMC5_.QVD] (qvd) is never read. How to make it correctly?

Subrutines in a LOAD

rwunderlich — Sat, 23 May 2009 06:29:25 GMT

You can't imbed the SQL Select in the middle of a load like that. What you can do is SELECT the fields from HNUMC1 as one table and then JOIN with the HNUMC5_.qvd.

-Rob

Subrutines in a LOAD

Mon, 25 May 2009 22:28:12 GMT

Hi, Rob. Do you mean i shouldn't use the HNUMC1 as a QVD file?

Subrutines in a LOAD

johnw — Wed, 27 May 2009 00:41:24 GMT

I doubt that he's saying that. Looking at your example, there's nothing to indicate that HNUMC1 is a QVD file, so I suspect he assumed that it was some SQL database table, same as I did. If it's a QVD file, there's no reason you can't use the QVD file. And in general, my recommendation is to make QVD files rather than having applications read directly from databases.

Subrutines in a LOAD

Wed, 27 May 2009 01:00:03 GMT

Well, the HNUMC1 and the HNUMC5 are QVD files, excuse me if i wasn't clear. I followed the Rob's recommendation (to make a JOIN) but if have problems with the sintaxis. Yes, John, i've your recommendation in this way:

1. Loaded the all data from the each DB tables to general QVD files without any calculations.

2. Loading the data form de Generals QVD files to news QVD proccesing data and adding special columns.

3. To Make an application using those new QVD files.

My problem is in the point 2. In my last example i'm trying to make a select in each row that i'm loading. So, Robs say that what i can do is to make a JOIN betwen both HNUMC1 and HNUMC5 (QVD files).

Subrutines in a LOAD

johnw — Wed, 27 May 2009 03:05:30 GMT

This probably isn't relevant to the question at hand, but at our shop we decided to skip your #1 and go straight to #2 from the database. The reasoning was that there was nothing gained by having the intermediate QVD files if the only application reading them was a single other application creating the QVD files that the applications would actually use. That may or may not apply to your situation, and has nothing to do with solving your current problem, so...

...back to the original question. I'm not fond of listing the same fields over and over, but I think something like this would work:

HNUMC5:

LOAD

HnuD01_01A

,HnuD02_01M

,HnuD04_01E

,HnuD05_01S

,HnuD06_01M

,HnuD08_01R

,HnuD13_01M

,HnuD15_01M

,HnuD17_01E

,HnuD1CId

,HnuD25_01T

,HnuD26_01C

,HnuD27_01D

,HnuD28_01G

,HnuD29_01T

,HnuD39_01E

,HnuD40_01E

,HnuD44_01S

,HnuD45_01T

,HnuD60_01S

,HnuD61_01F

,HnuD67_01C

,HnuD01_01A * 100 + HnuD02_01M as Periodo

,if(HnuD10_01A=1405 or HnuD10_01A=1406,HNUMC5SMN * (-1),0) as C_MoraContable

FROM [HNUMC5_.qvd] (qvd)

;

LEFT JOIN ([HNUMC5])

LOAD

HnuD01_01A

,HnuD02_01M

,HnuD04_01E

,HnuD05_01S

,HnuD06_01M

,HnuD08_01R

,HnuD13_01M

,HnuD15_01M

,HnuD17_01E

,HnuD1CId

,HnuD25_01T

,HnuD26_01C

,HnuD27_01D

,HnuD28_01G

,HnuD29_01T

,HnuD39_01E

,HnuD40_01E

,HnuD44_01S

,HnuD45_01T

,HnuD60_01S

,HnuD61_01F

,HnuD67_01C

,rangesum(0,sum(if(match(HnuD25_01T,3,4,5,6),HnuMC1SMN * -1))) as C_PromPatrimonio

FROM [HNUMC1_.qvd] (qvd)

GROUP BY

HnuD01_01A

,HnuD02_01M

,HnuD04_01E

,HnuD05_01S

,HnuD06_01M

,HnuD08_01R

,HnuD13_01M

,HnuD15_01M

,HnuD17_01E

,HnuD1CId

,HnuD25_01T

,HnuD26_01C

,HnuD27_01D

,HnuD28_01G

,HnuD29_01T

,HnuD39_01E

,HnuD40_01E

,HnuD44_01S

,HnuD45_01T

,HnuD60_01S

,HnuD61_01F

,HnuD67_01C

;

DROP FIELDS

HnuD06_01M

,HnuD08_01R

,HnuD13_01M

,HnuD15_01M

,HnuD17_01E

,HnuD1CId

,HnuD25_01T

,HnuD26_01C

,HnuD27_01D

,HnuD28_01G

,HnuD29_01T

,HnuD39_01E

,HnuD40_01E

,HnuD44_01S

,HnuD45_01T

,HnuD60_01S

,HnuD61_01F

,HnuD67_01C

;