Straight Table filtering

[#1]

Hi,

Below is the code that I used to load PO with trackCode BOOKCONF or BOOKED.

I want to look for BOOKED first, if not found use BOOKCONF.

How should I code. Appreciate your help.

Load  
  Date(trackDate) as StatusDate,    fileNumber , trackCode,  loggedBy as POConfirmBy
FROM [TrackTrace.qvd] (qvd)
where trackDate >= '01-01-2015' and MATCH(trackCode,'BOOKCONF','BOOKED') ;

Straight Table result. I only want BOOKED if both BOOKED & BOOKCONF are found for OMSPO# 00065849 .

[Gysbert_Wassenaar]

Please post a small qlikview document that demonstrates the issue

[pokassov]

Hi!

Something like that:

1. Load rows with trcakCode='BOOKED'

2. Load rows with trcakCode='BOOKCONF' where not exist key rows.

t1:

Load  
  Date(trackDate) as StatusDate,    fileNumber , trackCode,  loggedBy as POConfirmBy,

trackDate&'|'&fileNumber&'|'&loggedBy          as ID
FROM [TrackTrace.qvd] (qvd)
where trackDate >= '01-01-2015' and MATCH(trackCode,'BOOKED') ;

concatenate (t1)

Load  
  Date(trackDate) as StatusDate,    fileNumber , trackCode,  loggedBy as POConfirmBy
FROM [TrackTrace.qvd] (qvd)
where trackDate >= '01-01-2015' and MATCH(trackCode,'BOOKCONF')

and not exits(ID, trackDate&'|'&fileNumber&'|'&loggedBy);

[#4]

It show only PO with BOOKED status, but some PO with only BOOKCONF status didn't appear.

The logic is if BOOKED and BOOKCONF is found, take BOOKED. else if only BOOKCONF is found, take COONCONF.

Thank you. if

[pokassov]

As I can see in your word there is a field OMSPO# that I can use like a key.

Then:

t1:

Load  
  Date(trackDate) as StatusDate,    fileNumber , trackCode,  loggedBy as POConfirmBy,

     OMSPO#
FROM [TrackTrace.qvd] (qvd)
where trackDate >= '01-01-2015' and MATCH(trackCode,'BOOKED') >0;

concatenate (t1)

Load  
  Date(trackDate) as StatusDate,    fileNumber , trackCode,  loggedBy as POConfirmBy
FROM [TrackTrace.qvd] (qvd)
where trackDate >= '01-01-2015' and MATCH(trackCode,'BOOKCONF')>0

and not exits(OMSPO#);

drop field OMSPO#;

[#6]

Hi Sergey,

The linked field is fileNumer, so I tried below code but still failed :

:

Load

Date(trackDate) as StatusDate, fileNumber , trackCode, loggedBy as POConfirmBy

FROM (qvd)

where trackDate >= '01-01-2015'

and MATCH(trackCode,'BOOKED') ;

Concatenate

Load

Date(trackDate) as StatusDate, fileNumber , trackCode, loggedBy as POConfirmBy

FROM (qvd)

where trackDate >= '01-01-2015' and MATCH(trackCode,'BOOKCONF') and not Exists (fileNumber);

fileNumber

POConfirmBy

StatusDate

trackCode

OMSPO#

100142173

jiweil

01/02/2016

BOOKED

000013961

100142173

omslife

03/02/2016

BOOKCONF

000013961

100142173

omslife

04/02/2016

BOOKCONF

000013961

100142461

jiweil

03/02/2016

BOOKED

000013987

100142461

omslife

04/02/2016

BOOKCONF

000013987

100142462

jiweil

03/02/2016

BOOKED

000013988

100142462

omslife

04/02/2016

BOOKCONF

000013988

Expected result should be as follow :

fileNumber

POConfirmBy

StatusDate

trackCode

OMSPO#

100142173

jiweil

01/02/2016

BOOKED

000013961

100142461

jiweil

03/02/2016

BOOKED

000013987

100142462

omslife

04/02/2016

BOOKCONF

000013988

100142463

jiweil

03/02/2016

BOOKED

000013989

For 10014262, BOOKCONF appear as there in only 1 fileNumber with BOONCONF.

The rest has 2 lines BOOKED, BOOKCONF, thus only BOOKED will be taken.

I have attached the full code for your reference.

Appreciate your help.

Regards,

Andros Goh

[pokassov]

Hi!

The problem is you have already loaded field "fileNumber".

If you change your script to (bellow) it will work:

...

[T1]:

Load  

              Date(trackDate) as StatusDate,    fileNumber as fileNumber_id, trackCode,  loggedBy  as POConfirmBy

FROM [TrackTrace.qvd] (qvd)

where trackDate >= '01-01-2015'

and MATCH(trackCode,'BOOKED') ;

Concatenate

Load  

         Date(trackDate) as StatusDate,    fileNumber_id , trackCode,  loggedBy  as POConfirmBy

FROM [TrackTrace.qvd] (qvd)

where trackDate >= '01-01-2015' and MATCH(trackCode,'BOOKCONF') and not Exists (fileNumber_id,fileNumber);

rename table [T1] into temp;

[T1]:

noconcatenate load

StatusDate,

fileNumber_id     as fileNumber,

trackCode,

POConfirmBy

resident temp;

drop table temp;

...

[#8]

Hi Sergey,

Thank you. Can I ask you 1 more question :

How to hardcode the column TransportMode to filter on B, C, V and blank when I select BOOKED ?

I tried Ass Calculated Dimension, but it didn’t work. I believe I need another IF statement in the Expression ?

Appreciate your help.

[cid:image002.png@01D1BB87.F01804A0]

Regards,

Andros Goh

[pokassov]

Hi!

If I understand your question correctly then:

if(trackCode='BOOKED',null(),If(Match(TrasportMode,'B','C','V')>0,TrasportMode))

[#10]

Hi Sergey,

I tried this statement :

:

load fileNumber,

if(trackCode='BOOKED',null(),If(Match(TransportMode,'B','C','V',' ')>0,TransportMode)),

if(trackCode='BOOKCONBF',null(),If(Match(TransportMode,'A','B','T')>0,TransportMode))

resident ;

But I only received fileNumber with trackCode=’BOOKED, how should I change the code.

My desire result is to get filenNmber where trackCode = BOOKED and TransportMode = B, C, V

And fileNumber where trackCode = BOOKCONF and TransportMode = A, P, T.

Appreciate your help. Thank you.,

Regards,

Andros Goh