Case When

[Anonymous]

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

[marcus_sommer]

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

[kumarkp412 · Accepted solution]

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

[Anil_Babu_Samineni]

You may try this

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

[trdandamudi]

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')

[Anonymous]

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.

[Anonymous]

//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;