topic Re: Case When in QlikView

Case When

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Case When

marcus_sommer — Fri, 18 Nov 2016 13:53:32 GMT

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

Re: Case When

kumarkp412 — Fri, 18 Nov 2016 13:58:11 GMT

hi John,

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

Just try the below script in QV.

Temp:

Load

CreatedDate,

from table1.QVD

Join()

Load

updatedDate,

from table2.QVD

Final:

Load

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

resident Temp;

Thanks

Kumar kVP

Re: Case When

Anil_Babu_Samineni — Fri, 18 Nov 2016 13:58:38 GMT

You may try this

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

Re: Case When

trdandamudi — Fri, 18 Nov 2016 14:01:31 GMT

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

Re: Case When

Anonymous — Fri, 18 Nov 2016 15:09:10 GMT

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.

Re: Case When

Anonymous — Fri, 18 Nov 2016 15:16:32 GMT

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