topic Re: adding column in table in script in QlikView

adding column in table in script

Wed, 25 Nov 2020 16:16:04 GMT

Hi,

I have table created from SQL query in the script , I want to add an extra column in this table , this column is based on Previous function in Qlikview , bellow is an example

Order:

SQL

Select Col1,Col2

FROM

Order;

I want to add below third column in Order table inside script so final table should have three columns

Col3=Previous(Col1)

Re: adding column in table in script

johnca — Thu, 01 Sep 2016 03:06:05 GMT

Maybe this?

Temp:

Select

Col1,

Col2

From OrderDB;

Final:

NoConcatenate

Load

Col1,

Col2

Peek(Col1) as Col3

Resident Temp

Order By Col1 asc ;

Drop Table Temp;

Re: adding column in table in script

tamilarasu — Thu, 01 Sep 2016 03:13:31 GMT

Hi Vibhu,

You can also try,

Order:
LOAD *, Previous(Col1) as Col3;
SQL
Select Col1,Col2
FROM
Order;

Re: adding column in table in script

Anonymous — Thu, 01 Sep 2016 06:47:02 GMT

Hi Vibhu, Previuos with order by don't works as exprected sometimes, use peek better.

LEFT JOIN (Order)

LOAD

Col1,

Col2,

Peek(Col2) as Col3

RESIDENT Order

ORDER BY Col1, Col2;

Regards!

Re: adding column in table in script

tamilarasu — Thu, 01 Sep 2016 06:59:30 GMT

Hi Manuel,

Do you have any example that shows the behaviour.? I would like to know.

Thank you.

Re: adding column in table in script

Anonymous — Thu, 01 Sep 2016 07:16:38 GMT

Hi Tamil,

When you load a resident table with a previous statement, and you order your table at the same load statement, previous don't works as expected, because order by is done later than previous, peek is calculated after order.

Regards!

Re: adding column in table in script

johnca — Thu, 01 Sep 2016 11:55:51 GMT

As elegant as the preceding load looks, my solution seems the simplest...and works. BTW, I did forget one comma after Col2 in the resident load, so the code should look like

Temp:

Select

Col1,

Col2

From Order;

Order:

NoConcatenate

Load

Col1,

Col2,

Peek(Col1) as Col3

Resident Temp

Order By Col1 asc ;

Drop Table Temp;

Re: adding column in table in script

Anonymous — Thu, 01 Sep 2016 12:39:19 GMT

Hi John,

I don't do a preceding load, I am doing a left join to join all data in the same table.

Regards!

Re: adding column in table in script

johnca — Thu, 01 Sep 2016 14:49:27 GMT

Right Manuel, but Tamil's does, and is to what I was referring to. I responded to the wrong suggested solution...sorry about that. Your solution indeed looks simpler, but is a left join more efficient than my solution, especially for large data sets?

--john

Re: adding column in table in script

tamilarasu — Thu, 01 Sep 2016 14:49:43 GMT

Thank you, Manuel. I will test this tomorrow.