Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I want below SQL Query logic in to Qlikview...
select top 3 CV.ValueExcellentDealer,ll.Price,ll.DealerId,D.ID,cv.CarVersionId,ll.Kilometers,Kms,Year(cv.CarYear),Year(ll.MakeYear),D.CityId,cv.CityId,cv.id
from CarValuations as cv
join LiveListings as ll on cv.CarVersionId = ll.VersionId and Year(cv.CarYear) = Year(ll.MakeYear) and ll.sellertype = 1
join Dealers D on D.ID = ll.DealerId
where ll.Inquiryid = 857654
and D.CityId = cv.CityId
and Kms between (ll.Kilometers * 0.9) And (ll.Kilometers * 1.1)
order by cv.id desc
SQL OUTPUT:
ValueExcellentDealer | Price | DealerId | ID | CarVersionId | Kilometers | Kms | Year | CityId | id |
89500 | 95000 | 5488 | 5488 | 435 | 59047 | 57000 | 2003 | 10 | 35822922 |
89500 | 95000 | 5488 | 5488 | 435 | 59047 | 60000 | 2003 | 10 | 35120179 |
89500 | 95000 | 5488 | 5488 | 435 | 59047 | 60000 | 2003 | 10 | 35110740 |
Qlikview application has been attached for reference. i am facing challenge to handle kilometer filed for joining..
Hi,
The qvw shared does not have Inquiryid = 857654.
Please share the sample raw qvds and the desired output.
Hi jemimah,
Please find the attached app..
Thanks!
Regards,
Mayuri
!
desire output should be same as sql output
SQL OUTPUT:
ValueExcellentDealer | Price | DealerId | ID | CarVersionId | Kilometers | Kms | Year | CityId | id |
89500 | 95000 | 5488 | 5488 | 435 | 59047 | 57000 | 2003 | 10 | 35822922 |
89500 | 95000 | 5488 | 5488 | 435 | 59047 | 60000 | 2003 | 10 | 35120179 |
89500 | 95000 | 5488 | 5488 | 435 | 59047 | 60000 | 2003 | 10 | 35110740 |
Please help..
Their are couple of way to do it, according to your requirement :
Final_Table:
Load ValueExcellentDealer,
CarVersionId&'-'&CarYear as Key,
CarVersionId,
CarYear,
CityId as CarValuations_City,
CityId,
id;
from
SQL select
ValueExcellentDealer,
CarVersionId,
CityId ,
id
CarValuations;
join(Final_Table)
Load Price,
VersionId&'-'&MakeYear as Key,
DealerId,
MakeYear;
from
SQL select
VersionId,
MakeYear,
DealerId
LiveListings
where
sellertype = 1 and Inquiryid = '857654'
and Kms =>(Kilometers * 0.9) And Kms <=(Kilometers * 1.1);
inner join(Final_Table)
Load ID as Dealers_ID,
CityId,
CityId as Dealers_City;
from
SQL select
ID,
CityId
Dealers;
Hi Avinash R,
KMS & Kilometers both are in different tables.
I have two tables. if City, ValuationId, Year & Kilometers are same in both the tables then i want price & value
there is Kilometers filed i have challenge as many times it will match but moreover in both tables kilometers will not match so the below condition to be apply
Kms between (ll.Kilometers * 0.9) And (ll.Kilometers * 1.1)
how to do it
Hi Avinash R,
KMS & Kilometers both are in different tables.
I have two tables. if City, ValuationId, Year & Kilometers are same in both the tables then i want price & value
there is Kilometers filed i have challenge as many times it will match but moreover in both tables kilometers will not match so the below condition to be apply
Kms between (ll.Kilometers * 0.9) And (ll.Kilometers * 1.1)
how to do it