I need to create a subquery in the select

[Aleja54]

Hi all
I need to create columns, actually several, but coming from a subquery in qlik

SELECT `code` as r, `bill` as f, ifnull(null , (select `date` from tbl_per where code = r and bill = f limit 1)) as date ,
(select `amount` from tbl_per where code = r and bill = f limit 1) as amount,
(select `fecha` from tbl_per where code = r and bill = f limit 1 offset 1) as date ,

(select `amount` from tbl_per where code = r and bill = f limit 1 OFFSET 1) as amount
FROM `tbl_per`
GROUP by `rut`, `bill`;

which in qlik would look something like this

[tbl_per]:

LOAD `amount`,
`code`,
`bill`,
`date`,
`newdate,

`newamount`
;
SQL SELECT

`amount`,
`code`,
`bill`,
`date`,
 ifnull(null , (select `date` from tbl_per where code = r and bill = f limit 1)) as date,

select `amount` from tbl_per where code = r and bill = f limit 1

FROM `shops`.`tbl_per` ;

STORE tbl_per INTO lib://mySQL Database Conection2//tbl_per.qvd(qvd);

but it gives me an error

17/5000

any suggestions?

[simonaubert · Accepted solution]

Can you try :

[tbl_per]:

LOAD amount,
r as code,
f as bill,
[date],
newdate,
newamount
;

SQL SELECT `code` as r, `bill` as f, ifnull(null , (select `date` from tbl_per where code = r and bill = f limit 1)) as date ,
(select `amount` from tbl_per where code = r and bill = f limit 1) as amount,
(select `fecha` from tbl_per where code = r and bill = f limit 1 offset 1) as newdate ,
(select `amount` from tbl_per where code = r and bill = f limit 1 OFFSET 1) as newamount
FROM `tbl_per`
GROUP by `rut`, `bill`;

STORE tbl_per INTO lib://mySQL Database Conection2//tbl_per.qvd(qvd);

[Aleja54]

this work!!! thanks!!!