Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Aleja54
New Contributor

I need to create a subquery in the select

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

 
any suggestions?
Labels (3)
1 Solution

Accepted Solutions
Partner
Partner

Re: I need to create a subquery in the select

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);

2 Replies
Partner
Partner

Re: I need to create a subquery in the select

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);

Highlighted
Aleja54
New Contributor

Re: I need to create a subquery in the select

this work!!! thanks!!!