Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Now Live: Qlik Sense SaaS Simplified Authoring – Analytics Creation for Everyone: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
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]

1 Solution

Accepted Solutions
eduardo_sommer
Partner - Specialist
Partner - Specialist

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

View solution in original post

10 Replies
eduardo_sommer
Partner - Specialist
Partner - Specialist

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

Eduardo

Not applicable
Author

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
Partner - Specialist
Partner - Specialist

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
Author

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
Partner - Specialist
Partner - Specialist

No, from QVD files you use a Load command

Eduardo

Not applicable
Author

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

eduardo_sommer
Partner - Specialist
Partner - Specialist

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
Creator III
Creator III

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
Author

Thank you this way worked