Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Announcing the newest addition to the Qlik Community, Qlik Gallery! Learn More
mrjohn23
Contributor III

Case When

I have two tables stored as QVDs, see below. In SQL server I would create the below script to get my desired results. How can I write this in QV? Unfornutely, I cannot write the script in SQL server becuase my two tables are loaded on different serves and I cannot join to multiple servers for policy reasons.

,CASE WHEN DATEDIFF(DD,A.CreatedDate, B.UpdatedDate) > 3 THEN 'N'

  ELSE 'Y' END 'Indicator'

QVDs:

table 1                 table2

CreatedDate         UpdatedDate

ID                          ID

Tags (2)
1 Solution

Accepted Solutions
Highlighted
kumarkp412
Contributor II

Re: Case When

hi John,

Load these two tables in QV and join them using ID.

Just try the below script in QV.

Temp:

Load

CreatedDate,

ID

from table1.QVD

Join()

Load

updatedDate,

ID

from table2.QVD

Final:

Load

ID

if(interval(CreatedDate-UpdatedDate,'d')>3,N,Y) Indicator

resident Temp;

Thanks

Kumar kVP

6 Replies
MVP & Luminary
MVP & Luminary

Re: Case When

Maybe in this way:

qvd:

load ID, CreatedDate From qvd1;

     join

load ID, UpdatedDate From qvd2;

final:

load *, if(UpdatedDate - CreatedDate > 3, 'N', 'Y') as Indicator resident qvd;

drop table qvd;

- Marcus

Highlighted
kumarkp412
Contributor II

Re: Case When

hi John,

Load these two tables in QV and join them using ID.

Just try the below script in QV.

Temp:

Load

CreatedDate,

ID

from table1.QVD

Join()

Load

updatedDate,

ID

from table2.QVD

Final:

Load

ID

if(interval(CreatedDate-UpdatedDate,'d')>3,N,Y) Indicator

resident Temp;

Thanks

Kumar kVP

Re: Case When

You may try this

load *, if(interval(UpdatedDate - CreatedDate) > num(makedate(3)), 'N', 'Y') as Indicator resident qvd;

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
trdandamudi
Honored Contributor II

Re: Case When

You can also do this on the front end as below:

Load * from Table1

Concatenate

Load * from Table2

Straight chart:

Dimensions:

ID,

CreateData

UpdatedDate

Expression:

If(UpdatedDate-CreatedDate>3,'N','Y')

mrjohn23
Contributor III

Re: Case When

Thank you for all the feedback. This is what I have so far, cannot quite get the syntax correct but understand the idea.

qvd1:

sql

select * from TableA;

Store qvd1 INTO $vPath_QVDs)qvd1.qvd(qvd);

//////////////////////////////////////////////////////////////////////////

qvd2:

sql

select * from TableB;

Store qvd2 INTO $vPath_QVDs)qvd2.qvd(qvd);

////////////////////////////////////////////////////////////////////////////////////

Final:

Join(qvd2)

LOAD *, IF(UPDATE-CREATEDATE >3, 'N', 'Y') AS INDICATOR

RESIDENT qvd1 ;

DROP TABLE qvd1;

DROP TABLE qvd2;

//The Final: syntax is not working properly.

mrjohn23
Contributor III

Re: Case When

//Update: The below works.

qvd1:

sql

select * from TableA;

Store qvd1 INTO $vPath_QVDs)qvd1.qvd(qvd);

//////////////////////////////////////////////////////////////////////////

qvd2:

sql

select * from TableB;

Store qvd2 INTO $vPath_QVDs)qvd2.qvd(qvd);

////////////////////////////////////////////////////////////////////////////////////

Final:

Join(qvd1)

LOAD *, IF(UPDATE-CREATEDATE >3, 'N', 'Y') AS INDICATOR

RESIDENT qvd2 ;

//DROP TABLE qvd1;

DROP TABLE qvd2;