Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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);
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);
this work!!! thanks!!!