topic Straight Table filtering in QlikView

Straight Table filtering

Wed, 25 Nov 2020 16:16:04 GMT

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 .

Re: Straight Table filtering

Gysbert_Wassenaar — Wed, 25 May 2016 07:48:47 GMT

Please post a small qlikview document that demonstrates the issue

Re: Straight Table filtering

pokassov — Wed, 25 May 2016 08:08:05 GMT

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

Re: Straight Table filtering

Wed, 25 May 2016 09:06:48 GMT

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

Re: Straight Table filtering

pokassov — Wed, 25 May 2016 12:09:08 GMT

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

Re: Straight Table filtering

Mon, 30 May 2016 06:26:00 GMT

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

Re: Straight Table filtering

pokassov — Tue, 31 May 2016 09:27:03 GMT

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;

...

Re: Straight Table filtering

Tue, 31 May 2016 14:01:28 GMT

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

Re: Straight Table filtering

pokassov — Wed, 01 Jun 2016 05:55:46 GMT

Hi!

If I understand your question correctly then:

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

Re: Straight Table filtering

Sun, 05 Jun 2016 12:40:18 GMT

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

Re: Straight Table filtering

Mon, 06 Jun 2016 14:05:36 GMT

Hi Sergey,

I tried your code, but the system hung at table T1. Pls see the attach script.

Appreciate your help again. Thank you.

Regards,

Andros Goh

Re: Straight Table filtering

pokassov — Tue, 07 Jun 2016 05:49:52 GMT

Hello!

See attach. Could you add a logfile next time?

Re: Straight Table filtering

Tue, 07 Jun 2016 09:23:38 GMT

Hi Sergey,

I tried but still can’t get the desired result. Pls see my result in EXCEL and the script.

I should have BOOKED & BOOKCONF lines appear in the result.

The logic is that if BOOKED & BOOKCONF are found, get the date from BOOKED and ignore BOOKCONF.

But if only BOOKCONF is found, then get the date from BOOKCONF.

Sorry, I couldn’t find the location of the Log file in QlikTech folder.

Thank you.

Regards,

Andros Goh

Re: Straight Table filtering

pokassov — Wed, 08 Jun 2016 04:09:25 GMT

Hello!

Let's do the next:

1. Replace qvd files with "inline load".

2. add some rows in the inline load that show possible situation for BOOKED & BOOKCONF

3. Attach qvw file with what you do above.

Re: Straight Table filtering

Thu, 09 Jun 2016 03:18:19 GMT

Hi Sergey,

Attached are the import files and QVD. Appreciate your help. Thank you.

Regards,

Andros Goh

Re: Straight Table filtering

pokassov — Thu, 09 Jun 2016 11:12:30 GMT

Hi!

Your problems are:

1. inner join instead of left join

2. where cause - you exclude some lines and so your cannot find BOOKCONF

I checked sript for T1 - there were lines with BOOKCONF.

Anywhere see attach with script.

Best regards

Re: Straight Table filtering

Sat, 11 Jun 2016 12:48:26 GMT

Hi Sergey,

Thank you very much, it work now.

· But when I select the period e.g. 2016/04 from the box, those item in T1 didn’t appear.

· If I didn’t make any selection, the records in T1 appear.

· I just join this T1 to main table ??

· Also I must remark the statement Drop table, else it won’t work.

Appreciate your help.

Without selection shown T1 records which is correct

With selection

Regards,

Andros Goh

Re: Straight Table filtering

pokassov — Tue, 14 Jun 2016 06:10:39 GMT

Hello!

If my advise is correct then could you close the topic and start a new one.

If you add your qvw to a new topic you will recieve an answer faster from community.

Best regards, Sergey