topic Re: SQL script! in QlikView

SQL script!

Wed, 25 Nov 2020 16:16:04 GMT

$(QVDatabaseTable_1):

LOAD * ;

SQL SELECT count(*) as NoOf_Rows

FROM $(DatabaseName).$(DatabaseUser).$(DatabaseTable_1)

$(INCREMENTAL_EXP);

Store $(QVDatabaseTable_1) into $(vQVDFileName_1)(qvd);

Drop Table $(QVDatabaseTable_1);

This just will not run... do anybody have an idea?

i have also tried just

SQL SELECT count(*)

also.

please let me know if I am doing anything wrong.

Thank you!!

Re: SQL script!

robert_mika — Tue, 06 Dec 2016 01:01:57 GMT

With aggregation, you need to use Group by the statement.

Something like:

SELECT somecolumn,someothercolumn, COUNT(*) FROM my_table GROUP BY somecolumn,someothercolumn

www.qliksteps.com

Re: SQL script!

jagan — Tue, 06 Dec 2016 08:11:33 GMT

Hi,

Check whether your variables are populating with expected values.

Regards,

Jagan.

Re: SQL script!

Anonymous — Tue, 06 Dec 2016 08:28:47 GMT

^^ I agree, Variables are most probably the issue. the rest look fine imo.

Re: SQL script!

maxgro — Tue, 06 Dec 2016 08:37:38 GMT

You can add some TRACE before the LOAD statement, something like

TRACE QVDatabaseTable_1=$(QVDatabaseTable_1);

TRACE DatabaseName=$(DatabaseName);

.....

to check you vars

Also you can post the error you get

Re: SQL script!

asgardd2 — Tue, 06 Dec 2016 08:47:32 GMT

Are your problem with sql query? (SELECT count(*) as NoOf_Rows ... )

Please, post error,which you get, when document is reloading.

Re: SQL script!

Tue, 06 Dec 2016 09:59:31 GMT

OUTER JOIN worked perfectly for me!! I have a followup question.

is it possible to make another column by adding or subtracting other two columns?

for example, let's say by Outer Joining I got three columns col1, col2, col3

Can I make col4 by col1 - col2?

Re: SQL script!

Anonymous — Tue, 06 Dec 2016 10:02:30 GMT

Yes, you can do that in the preceeding load

Table1:

Load

col1 - col2 as col4;

sql....;

Re: SQL script!

maxgro — Tue, 06 Dec 2016 10:08:08 GMT

if you have a table (Table) with a logical primary key (Id) with a join

left join (Table)

load

Id,

col1 - col2 as col4

resident Table;

or you can always do a resident load

NewTable:

Noconcatenate

Load

col1 - col2 as col4

resident Table;

drop table Table;