topic Re: Using If Condition in QlikView

Using If Condition

Wed, 24 May 2017 05:32:04 GMT

Hi Team,

I have a scenario like below

LOAD [Sales docu_DOC_NUMBER./BIC/AZDAT_O2300] & [Item_S_ORD_ITEM./BIC/AZDAT_O2300] as KEY,
[Network Ac_ACTIVITY./BIC/AZDAT_O2300],
[Network_NETWORK./BIC/AZDAT_O2300],
[Project De_PROJECT./BIC/AZDAT_O2300],
[WBS Elemen_WBS_ELEMT./BIC/AZDAT_O2300],
[Routing nu_PUR_AUFPL./BIC/AZDAT_O2300],
[Counter_/BIC/ZAPLZL./BIC/AZDAT_O2300],
[Sequence N_/BIC/ZSEQNO./BIC/AZDAT_O2300],
[RecordMode_RECORDMODE./BIC/AZDAT_O2300],
[Company co_COMP_CODE./BIC/AZDAT_O2300],
[Earliest S_/BIC/ZESHSDT./BIC/AZDAT_O2300],
[Earliest F_/BIC/ZEFINDT./BIC/AZDAT_O2300],
[Latest Sch_/BIC/ZLSHSDT./BIC/AZDAT_O2300],
if([Network Ac_ACTIVITY./BIC/AZDAT_O2300] = 1000 , [Actual Sta_/BIC/ZACTSDT./BIC/AZDAT_O2300]) as [MileStone 1000 Actual End Date],
if([Network Ac_ACTIVITY./BIC/AZDAT_O2300] = 2000 , [Actual Sta_/BIC/ZACTSDT./BIC/AZDAT_O2300]) as [MileStone 2000 Actual End Date],
if([Network Ac_ACTIVITY./BIC/AZDAT_O2300] = 3000 , [Actual Sta_/BIC/ZACTSDT./BIC/AZDAT_O2300]) as [MileStone 3000 Actual End Date],
if([Network Ac_ACTIVITY./BIC/AZDAT_O2300] = 4000 , [Actual Sta_/BIC/ZACTSDT./BIC/AZDAT_O2300]) as [MileStone 4000 Actual End Date],
if([Network Ac_ACTIVITY./BIC/AZDAT_O2300] = 5000 , [Actual Sta_/BIC/ZACTSDT./BIC/AZDAT_O2300]) as [MileStone 5000 Actual End Date],
if([Network Ac_ACTIVITY./BIC/AZDAT_O2300] = 6000 , [Actual Sta_/BIC/ZACTSDT./BIC/AZDAT_O2300]) as [MileStone 6000 Actual End Date],

Here am using a condition , and storing the data in different Columns,

And O/P is shown like below

But my expected O/P is all the data should be shown in single row

Ex :

\\How to Achieve this.?

Re: Using If Condition

Kushal_Chawda — Wed, 24 May 2017 05:54:48 GMT

try this

Data:

FROM Table:

left join(Data)

LOAD KEY,
if([Network Ac_ACTIVITY./BIC/AZDAT_O2300] = 1000 , [Actual Sta_/BIC/ZACTSDT./BIC/AZDAT_O2300]) as [MileStone 1000 Actual End Date],
if([Network Ac_ACTIVITY./BIC/AZDAT_O2300] = 2000 , [Actual Sta_/BIC/ZACTSDT./BIC/AZDAT_O2300]) as [MileStone 2000 Actual End Date],
if([Network Ac_ACTIVITY./BIC/AZDAT_O2300] = 3000 , [Actual Sta_/BIC/ZACTSDT./BIC/AZDAT_O2300]) as [MileStone 3000 Actual End Date],
if([Network Ac_ACTIVITY./BIC/AZDAT_O2300] = 4000 , [Actual Sta_/BIC/ZACTSDT./BIC/AZDAT_O2300]) as [MileStone 4000 Actual End Date],
if([Network Ac_ACTIVITY./BIC/AZDAT_O2300] = 5000 , [Actual Sta_/BIC/ZACTSDT./BIC/AZDAT_O2300]) as [MileStone 5000 Actual End Date],
if([Network Ac_ACTIVITY./BIC/AZDAT_O2300] = 6000 , [Actual Sta_/BIC/ZACTSDT./BIC/AZDAT_O2300]) as [MileStone 6000 Actual End Date]

Resident Data;

Re: Using If Condition

Wed, 24 May 2017 07:14:42 GMT

Hi Kushal,

Thanks for the reply. If we are using left join , only the MileStone 1000 will be displayed. The other values will be NULL

Re: Using If Condition

Kushal_Chawda — Wed, 24 May 2017 08:39:43 GMT

then do the left join for each if condition

Data:

FROM Table:

left join(Data)

LOAD KEY,
if([Network Ac_ACTIVITY./BIC/AZDAT_O2300] = 1000 , [Actual Sta_/BIC/ZACTSDT./BIC/AZDAT_O2300]) as [MileStone 1000 Actual End Date]

Resident Data;

left join(Data)

LOAD KEY,

if([Network Ac_ACTIVITY./BIC/AZDAT_O2300] = 2000 , [Actual Sta_/BIC/ZACTSDT./BIC/AZDAT_O2300]) as [MileStone 2000 Actual End Date]

Resident Data;

left join(Data)