Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Join us for a live Q&A! September 21, 10 AM ET - Onboarding Fast in Qlik Sense SaaS! REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Aleja54
Contributor
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
simonaubert
Partner
Partner

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

Bi Consultant (Dataviz & Dataprep) @ Business & Decision

View solution in original post

2 Replies
simonaubert
Partner
Partner

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

Bi Consultant (Dataviz & Dataprep) @ Business & Decision

View solution in original post

Aleja54
Contributor
Contributor
Author

this work!!! thanks!!!