Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
mayuridg
Contributor

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

Re: Help in Scripting

Hi,

The qvw shared does not have Inquiryid = 857654.

Please share the sample raw qvds and the desired output.

mayuridg
Contributor

Re: Help in Scripting

Hi jemimah,

Please find the attached app..

Thanks!

Regards,

Mayuri

mayuridg
Contributor

Re: Help in Scripting

!

mayuridg
Contributor

Re: Help in Scripting

desire output should be same as sql output

SQL OUTPUT:  

ValueExcellentDealerPriceDealerIdIDCarVersionIdKilometersKmsYearCityIdid
895009500054885488435590475700020031035822922
895009500054885488435590476000020031035120179
895009500054885488435590476000020031035110740
mayuridg
Contributor

Re: Help in Scripting

Please help..

Re: Help in Scripting

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;

mayuridg
Contributor

Re: Help in Scripting

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

mayuridg
Contributor

Re: Help in Scripting

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

Community Browser