Help in Scripting

[QlikAngel]

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..

[#2]

Hi,

The qvw shared does not have Inquiryid = 857654.

Please share the sample raw qvds and the desired output.

[QlikAngel]

Hi jemimah,

Please find the attached app..

Thanks!

Regards,

Mayuri

[QlikAngel]

!

[QlikAngel]

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

[QlikAngel]

Please help..

[avinashelite]

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;

[QlikAngel]

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

[QlikAngel]

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