topic Help in Scripting in QlikView

Help in Scripting

QlikAngel — Tue, 14 Jul 2015 06:40:06 GMT

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

Re: Help in Scripting

Tue, 14 Jul 2015 07:16:24 GMT

Hi,

The qvw shared does not have Inquiryid = 857654.

Please share the sample raw qvds and the desired output.

Re: Help in Scripting

QlikAngel — Tue, 14 Jul 2015 08:06:02 GMT

Hi jemimah,

Please find the attached app..

Thanks!

Regards,

Mayuri

Re: Help in Scripting

QlikAngel — Tue, 14 Jul 2015 08:13:29 GMT

Re: Help in Scripting

QlikAngel — Tue, 14 Jul 2015 08:14:19 GMT

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

Re: Help in Scripting

QlikAngel — Tue, 14 Jul 2015 11:56:45 GMT

Please help..

Re: Help in Scripting

avinashelite — Tue, 14 Jul 2015 13:25:06 GMT

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 ,

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;

Re: Help in Scripting

QlikAngel — Wed, 15 Jul 2015 04:57:43 GMT

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

Re: Help in Scripting

QlikAngel — Wed, 15 Jul 2015 04:59:02 GMT

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