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

Problem with sql code to qlikview code

I have a sql code, but i need to make something like it in qlikview. I tried different ways, but i stuck, someone got an ideas how can it be done.

SELECT

[ContactId]

,[PolicyEndDate]

,[policyDate]

,[PolicyNo]

,[source]

,[policttype]

into #Candidates

from Policy WITH (nolock)

select

[ContactID]

from #Candidates e

inner join #Candidates s

ON

  1. e.contactid = s.contactid

AND e.policytype = s.policytype

AND e.source = s.source

WHERE

ABS(DATEDIFF(DAY,s.PolicyDate , e.PolicyEndDate)) < 30

AND

(e.Policy <> s.Policy)

GROUP BY

[ContactID]

Tags (1)
1 Solution

Accepted Solutions
eduardo_sommer
Valued Contributor

Re: Problem with sql code to qlikview code

Try this:

TempTable:

Load

  [ContactID],

  policytype,

  source,

  PolicyDate,

  PolicyEndDate,

  Policy

From QVDFile.qvd (qvd);

join(TempTable)

load [ContactID]

  policytye,

  source,

  PolicyDate as PolicyDateS

  Policy as PolicyS

Resident TempTable;

Table:

Load [ContactID]

Resident TempTable

Where Fabs(PolicyDateS - PolicyDateEnd) < 30

   and Policy <> PolicyS;

Eduardo

10 Replies
eduardo_sommer
Valued Contributor

Re: Problem with sql code to qlikview code

I think the problem is the INTO clause in the first select.

Eduardo

Not applicable

Re: Problem with sql code to qlikview code

I just need to transform some how sql code into qlikview code, maybe i make a mistake typing sql code, but it is not the problem i am searching to solve.

eduardo_sommer
Valued Contributor

Re: Problem with sql code to qlikview code

If I understood your sql code, your query is doing in two steps what can be done in just one. Use only the second SELECT, with FROM POLICY and INNER JOIN POLICY instead of FROM #CANDIDATES and INNER JOIN #CANDIDATES

I think this wil work

Eduardo

Not applicable

Re: Problem with sql code to qlikview code

The problem is i don't know how can i make somefing like

/////

select

[ContactID]

from #Candidates e

inner join #Candidates s

ON

  1. e.contactid = s.contactid

AND e.policytype = s.policytype

AND e.source = s.source

WHERE

ABS(DATEDIFF(DAY,s.PolicyDate , e.PolicyEndDate)) < 30

AND

(e.Policy <> s.Policy)

/////

in qlikview. I can make sql select, but how can i make this work whight qvd files?

eduardo_sommer
Valued Contributor

Re: Problem with sql code to qlikview code

No, from QVD files you use a Load command

Eduardo

Not applicable

Re: Problem with sql code to qlikview code

I know that, but i don't know how can i make something similar to this sql script.

eduardo_sommer
Valued Contributor

Re: Problem with sql code to qlikview code

Try this:

TempTable:

Load

  [ContactID],

  policytype,

  source,

  PolicyDate,

  PolicyEndDate,

  Policy

From QVDFile.qvd (qvd);

join(TempTable)

load [ContactID]

  policytye,

  source,

  PolicyDate as PolicyDateS

  Policy as PolicyS

Resident TempTable;

Table:

Load [ContactID]

Resident TempTable

Where Fabs(PolicyDateS - PolicyDateEnd) < 30

   and Policy <> PolicyS;

Eduardo

bruno_montenegr
Contributor III

Re: Re: Problem with sql code to qlikview code

Hello.

Try something like this:

Candidates_Temp:

Load

    contactid,

    policytype,

    source,

    PolicyDate,

    PolicyEndDate,

    Policy

From Candidates.qvd (qvd);


Inner join (Candidates_Temp) Load

    contactid,

    policytype,

    source,

    PolicyDate as PolicyDate2,

    PolicyEndDate as PolicyEndDate2,

    Policy as Policy2

From Candidates.qvd (qvd);


Candidates:

Load Distinct

     contactid

Resident Candidates_Temp

Where Fabs(PolicyDate2 - PolicyEndDate) < 30

     and Policy <> Policy2;


Drop Table Candidates_Temp;


Hope it helps.

Not applicable

Re: Problem with sql code to qlikview code

Thank you this way worked

Community Browser