Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
QlikAngel
Partner - Creator
Partner - Creator

Help in Scripting

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:   

ValueExcellentDealerPriceDealerIdIDCarVersionIdKilometersKmsYearCityIdid
895009500054885488435590475700020031035822922
895009500054885488435590476000020031035120179
895009500054885488435590476000020031035110740

Qlikview application has been attached for reference. i am facing challenge to handle kilometer filed for joining..

8 Replies
Not applicable

Hi,

The qvw shared does not have Inquiryid = 857654.

Please share the sample raw qvds and the desired output.

QlikAngel
Partner - Creator
Partner - Creator
Author

Hi jemimah,

Please find the attached app..

Thanks!

Regards,

Mayuri

QlikAngel
Partner - Creator
Partner - Creator
Author

!

QlikAngel
Partner - Creator
Partner - Creator
Author

desire output should be same as sql output

SQL OUTPUT:  

ValueExcellentDealerPriceDealerIdIDCarVersionIdKilometersKmsYearCityIdid
895009500054885488435590475700020031035822922
895009500054885488435590476000020031035120179
895009500054885488435590476000020031035110740
QlikAngel
Partner - Creator
Partner - Creator
Author

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
Partner - Creator
Partner - Creator
Author

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
Partner - Creator
Partner - Creator
Author

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