topic Re: Re: Add values to sql query in QlikView

Add values to sql query

Tue, 18 Nov 2014 13:23:00 GMT

Helo guys,

So I have this SQL query that regurts a set of number (1,2,3,4,5,6....,1999,2000,2001) .

I would like to add to these results 3 more values, for example, if I have 1,2,3,4,5,6....,1999,2000,2001 and I add the values I would get 1,2,3,4,5,6....,1999,2000,2001,2002,2003,2004.

How can I achieve this?

Thank you in advance!!

Best regards.

Re: Add values to sql query

maleksafa — Tue, 18 Nov 2014 13:33:47 GMT

based on what you need to add the values, what is the pattern?

Re: Add values to sql query

Tue, 18 Nov 2014 13:43:06 GMT

This is to be used on a table where I see values in a FIFO system.

The values I get from my query are things that already happened (green), and the values I want to add are things that are going to happen (grey).

If there is a better way to achieve this without messing with the query I wouldn't mind

Re: Add values to sql query

anbu1984 — Tue, 18 Nov 2014 13:56:15 GMT

Table:

Load Sequence,Number,Time From Table

Concatenate

Load Max(Sequence)+1 As Sequence Resident Table

Concatenate

Load Max(Sequence)+2 As Sequence Resident Table

Concatenate

Load Max(Sequence)+3 As Sequence Resident Table

Re: Add values to sql query

Tue, 18 Nov 2014 14:11:10 GMT

Is the "Table" from "Load Sequence,Number,Time From Table"

what I use in the sql query?

Re: Add values to sql query

anbu1984 — Tue, 18 Nov 2014 14:17:26 GMT

QV_Table:

Sql Select Sequence,Number,Time From Table;

Concatenate

Load Max(Sequence)+1 As Sequence Resident QV_Table;

Concatenate

Load Max(Sequence)+2 As Sequence Resident QV_Table;

Concatenate

Load Max(Sequence)+3 As Sequence Resident QV_Table;

Re: Add values to sql query

Tue, 18 Nov 2014 14:40:27 GMT

Nop, I still get an error.

It seems like QV is looking for "Table" on the project's folder...

I don't understand why

Re: Add values to sql query

Marcio_Campestrini — Tue, 18 Nov 2014 14:54:40 GMT

Hi Ivo,

Change the "Table" In SQL command by your table name.

Márcio

Re: Add values to sql query

Tue, 18 Nov 2014 15:02:22 GMT

I use a lot of tables on my query (and the respective relation between foreign and primary keys).

But even with all those tables I still get the same error. It seems that QV is always looking for something on the folder.

Re: Add values to sql query

Marcio_Campestrini — Tue, 18 Nov 2014 15:08:29 GMT

Ivo,

You have to analyze your entire script and identify what (or whom) tables are needed. The example works only if you have all the fields coming from the same table, and the "Table" exist in the database.

If possible, share the qvw file so we can guide you.

Márcio

Re: Re: Add values to sql query

Wed, 19 Nov 2014 08:11:10 GMT

Hello Márcio,

in attachment there is a dummy qvw file. I had to remove the sql query because it would not work for you and I simulate this with a excel file where I load the values from.

I have QV personal edition so I won't be able to open a file edited with another version

Thank you in advance.

Best regards.

Re: Add values to sql query

jagan — Wed, 19 Nov 2014 08:21:39 GMT

Hi,

Try like this

QV_Table:

Sql Select Sequence,Number,Time From Table;

MaxValue:

LOAD

Max(Sequence) AS MaxSequence

RESIDENT QV_Table;

LET vMaxSequence = Peek('MaxSequence');

DROP TABLE MaxValue;

Concatenate(QV_Table)

LOAD

$(vMaxSequence) + RecNo() AS Sequence

Autogenerate(3);

Hope this helps you.

Regards,

Jagan.

Re: Re: Add values to sql query

Wed, 19 Nov 2014 08:37:44 GMT

Ok I finally understood how to use the example you guys provided, but is does not solve the problem, I still do not have the extra 3 values.

Re: Re: Add values to sql query

jagan — Wed, 19 Nov 2014 08:43:15 GMT

Hi,

Try this sample script

QV_Table:

LOAD

INLINE

[

Sequence, Dim

100,abc

101,def

102,ghi ];

MaxValue:

LOAD

Max(Sequence) AS MaxSequence

RESIDENT QV_Table;

LET vMaxSequence = Peek('MaxSequence');

DROP TABLE MaxValue;

Concatenate(QV_Table)

LOAD

$(vMaxSequence) + RecNo() AS Sequence

Autogenerate(3);

I am getting additional records with 103, 104 and 105.

Regards,

Jagan.

Re: Re: Add values to sql query

Wed, 19 Nov 2014 09:26:19 GMT

Hi Jagan,

So your solution worked well and I can add my extra three values to the query, but now I am wondering, what if I want to do someting like this (the N/A values forced):

Re: Re: Add values to sql query

jagan — Wed, 19 Nov 2014 09:58:39 GMT

Try this script

Concatenate(QV_Table)

LOAD

$(vMaxSequence) + RecNo() AS Sequence,

'N/A' AS Knr,

'N/A' AS time

Autogenerate(3);

Regards,

Jagan.

Re: Re: Add values to sql query

Wed, 19 Nov 2014 15:16:52 GMT

Thank you Jagan, it works well enough