topic syntax to add a condition in QlikView

syntax to add a condition

Tue, 19 Apr 2016 16:31:15 GMT

Hi all,

I need to add a condition to a field which is part of the LINKKEY field (Not sure why initially in one qvd LINKKEY is defined in LOAD part and in another in SELECT part).

QVD1---------------------------------------------------------------------------------------

condition to add:

if D_ASSET_TYPE = '005' and A_DEPT < '1000' then A_DEPTFUNC = '000'

QVD1:

LOAD
A_DEPT,
F_FUND,
A_DEPTFUNC,
D_ASSET_TYPE,
A_SUBDEPT,
LINKKEY;

SQL SELECT
A_DEPT,
F_FUND,
A_DEPTFUNC,
D_ASSET_TYPE,
A_SUBDEPT,
A_DEPT || '-' || F_FUND || '-' || A_DEPTFUNC || '-' || D_ASSET_TYPE || '-' || A_SUBDEPT as LINKKEY
FROM YYYY;

store QVD1 into '..\QVDs\QVD1.qvd';

QVD2---------------------------------------------------------------------------------------

condition to add:

if ASSET_TYPE = '005' and DEPT < '1000' then DEPTFUNC = '000'

QVD2:
LOAD
    DEPT,
    DEPTFUNC,
    DEPT & '-' & FUND & '-' & DEPTFUNC & '-' & ASSET_TYPE & '-' & SUBDEPT as LINKKEY,
    DEPT & '-' & DEPTFUNC & '-' & SUBDEPT as DEPTPROJKEY,
SQL SELECT
     DEPT,
     DEPTFUNC,
FROM AAAA;
store QVD2 into '..\QVDs\QVD2.qvd';

Thanks, appreciate your help

Re: syntax to add a condition

sunny_talwar — Tue, 19 Apr 2016 16:40:57 GMT

May be this:

QVD1:

LOAD
A_DEPT,
F_FUND,
A_DEPTFUNC,
D_ASSET_TYPE,
A_SUBDEPT,
DEPT & '-' & FUND & '-' & If(D_ASSET_TYPE = '005' AND A_DEPT = <1000, '000', DEPTFUNC) & '-' & ASSET_TYPE & '-' & SUBDEPT as LINKKEY;

SQL SELECT
A_DEPT,
F_FUND,
A_DEPTFUNC,
D_ASSET_TYPE,
A_SUBDEPT,
A_DEPT || '-' || F_FUND || '-' || A_DEPTFUNC || '-' || D_ASSET_TYPE || '-' || A_SUBDEPT as LINKKEY
FROM YYYY;

store QVD1 into '..\QVDs\QVD1.qvd';

QVD2:

LOAD

DEPT,

DEPTFUNC,

DEPT & '-' & FUND & '-' & If(D_ASSET_TYPE = '005' AND A_DEPT = <1000, '000', DEPTFUNC) & '-' & ASSET_TYPE & '-' & SUBDEPT as LINKKEY,

DEPT & '-' & DEPTFUNC & '-' & SUBDEPT as DEPTPROJKEY,

SQL SELECT

DEPT,

DEPTFUNC,

FROM AAAA;

store QVD2 into '..\QVDs\QVD2.qvd';

Re: syntax to add a condition

Tue, 19 Apr 2016 17:01:47 GMT

Sunny,

looks like we can not condition within select statement?

Re: syntax to add a condition

sunny_talwar — Tue, 19 Apr 2016 18:10:12 GMT

That would be a question to ask a SQL guy. Don't have too much expertise there.

Re: syntax to add a condition

Qrishna — Tue, 19 Apr 2016 19:38:26 GMT

Hi Larisa,

Try below:

QVD1:

LOAD
A_DEPT,
F_FUND,
A_DEPTFUNC,
D_ASSET_TYPE,
A_SUBDEPT,
LINKKEY;

SQL SELECT
A_DEPT,
F_FUND,
A_DEPTFUNC,
D_ASSET_TYPE,
A_SUBDEPT,
A_DEPT || '-' || F_FUND || '-' || A_DEPTFUNC || '-' || D_ASSET_TYPE || '-' || A_SUBDEPT as LINKKEY
FROM YYYY

WHERE D_ASSET_TYPE = '005' and A_DEPT < '1000' AND A_DEPTFUNC = '000';

//(if you want all the conditions to be satisfied at once else you can use 'OR' instead of 'AND')

store QVD1 into '..\QVDs\QVD1.qvd';

QVD2---------------------------------------------------------------------------------------

QVD2:

LOAD
    DEPT,
    DEPTFUNC,
    DEPT & '-' & FUND & '-' & DEPTFUNC & '-' & ASSET_TYPE & '-' & SUBDEPT as LINKKEY,
    DEPT & '-' & DEPTFUNC & '-' & SUBDEPT as DEPTPROJKEY,
SQL SELECT
     DEPT,
     DEPTFUNC,
FROM AAAA

where ASSET_TYPE = '005' and DEPT < '1000' and DEPTFUNC = '000';

store QVD2 into '..\QVDs\QVD2.qvd';

Re: syntax to add a condition

Wed, 20 Apr 2016 15:06:14 GMT

Thank you Sunny for your help, it works.

Re: syntax to add a condition

Wed, 20 Apr 2016 15:07:44 GMT

Thanks a lot, problem fixed.

Re: syntax to add a condition

Qrishna — Wed, 20 Apr 2016 15:16:00 GMT

Please close the thread