"Exclude 'TK' Document Type and All Records with Same Reference – Qlik Load Script Help"

[leylabalse]

Below, I have a Qlik script that works correctly, and I would like to make a modification to it.

While preparing the final dataset, all records where the BLART (Document Type) field is "TK" must be excluded, along with any other records that share the same XBLNR (Reference Number) as those "TK" document type entries.

How can this filtering logic be implemented?
What is the best approach to apply this rule in QlikView or Qlik Sense?

LET vStartDate = Date(YearStart(Today()), 'YYYYMMDD');
LET vEndDate = Date(YearEnd(Today()), 'YYYYMMDD');
CONTROLTK:
LOAD
BELNR AS BELGE_NO,
BUKRS AS SIRKET_KODU,
GJAHR AS YIL,
Date(BLDAT, 'DD.MM.YYYY') AS BELGE_TARIHI,
Date(BUDAT, 'DD.MM.YYYY') AS KAYITTARIH,
BLART AS BELGE_TURU,
USNAM AS OLUSTURAN_KULLANICI,
TCODE AS TRANSAKSIYON_KODU,
AEDAT AS DEGISIKLIK_TARIHI,
XBLNR AS REFERANS,
STBLG AS TERSBELGENO,
CPUDT AS ISLEM_TARIHI
WHERE
BLDAT <> BUDAT
;
SQL SELECT
BELNR,
BUKRS,
GJAHR,
BLDAT,
BLART,
USNAM,
TCODE,
AEDAT,
XBLNR,
STBLG,
CPUDT,
BUDAT
FROM BKPF
WHERE USNAM IN (
'USER6', 'USER7'
)
AND BUKRS = '2000'
AND BLDAT BETWEEN '$(vStartDate)' AND '$(vEndDate)'
AND TCODE NOT IN (
'KO88', 'ZCO011', 'ZCO007R1', 'MR21', 'AFABN', 'KSS2', 'KSII',
'KON2', 'KO8G', 'KKAO', 'CO88H', 'MR11', 'KSV5', 'KSU5',
'KE27', 'KEU5', 'CJ88', 'CJ8G', 'AFAB', 'KOC4', '/MBIS/ENF_P006', 'CK24'
)
AND TCODE IS NOT NULL
AND TCODE <> ''
AND BLART NOT IN ('XX', 'FX')
AND (
STBLG IS NULL
OR STBLG = ''
)
;

Original Data Set (Example)

BELNR	BLART	XBLNR	Description
10001	SA	REF001	Invoice
10002	TK	REF002	Invoice
10003	SA	REF002	Invoice
10004	SA	REF003	Invoice
10005	SA	REF004	Invoice
10006	TK	REF005	Invoice
10007	SA	REF005	Invoice
10008	TK	REF009	Invoice

Filtered Final Dataset (Desired Output)

BELNR	BLART	XBLNR	Description
10001	SA	REF001	Invoice
10004	SA	REF003	Invoice
10005	SA	REF004	Invoice

[Chanty4u]

Try this 

LET vStartDate = Date(YearStart(Today()), 'YYYYMMDD');

LET vEndDate = Date(YearEnd(Today()), 'YYYYMMDD');

CONTROLTK_RAW:

LOAD

    BELNR AS BELGE_NO,

    BUKRS AS SIRKET_KODU,

    GJAHR AS YIL,

    Date(BLDAT, 'DD.MM.YYYY') AS BELGE_TARIHI,

    Date(BUDAT, 'DD.MM.YYYY') AS KAYITTARIH,

    BLART AS BELGE_TURU,

    USNAM AS OLUSTURAN_KULLANICI,

    TCODE AS TRANSAKSIYON_KODU,

    AEDAT AS DEGISIKLIK_TARIHI,

    XBLNR AS REFERANS,

    STBLG AS TERSBELGENO,

    CPUDT AS ISLEM_TARIHI

WHERE

    BLDAT <> BUDAT

;

SQL SELECT

    BELNR,

    BUKRS,

    GJAHR,

    BLDAT,

    BLART,

    USNAM,

    TCODE,

    AEDAT,

    XBLNR,

    STBLG,

    CPUDT,

    BUDAT

FROM BKPF

WHERE USNAM IN (

    'USER6', 'USER7'

)

AND BUKRS = '2000'

AND BLDAT BETWEEN '$(vStartDate)' AND '$(vEndDate)'

AND TCODE NOT IN (

    'KO88', 'ZCO011', 'ZCO007R1', 'MR21', 'AFABN', 'KSS2', 'KSII',

    'KON2', 'KO8G', 'KKAO', 'CO88H', 'MR11', 'KSV5', 'KSU5',

    'KE27', 'KEU5', 'CJ88', 'CJ8G', 'AFAB', 'KOC4', '/MBIS/ENF_P006', 'CK24'

)

AND TCODE IS NOT NULL

AND TCODE <> ''

AND BLART NOT IN ('XX', 'FX')

AND (

    STBLG IS NULL OR STBLG = ''

);

TK_References:

LOAD DISTINCT

    REFERANS // this is XBLNR

RESIDENT CONTROLTK_RAW

WHERE BELGE_TURU = 'TK';

CONTROLTK:

LOAD *

RESIDENT CONTROLTK_RAW

WHERE NOT EXISTS(REFERANS, REFERANS)

AND BELGE_T

URU <> 'TK';

DROP TABLE CONTROLTK_RAW;

[leylabalse]

Hello @Chanty4u 

Thank you very much for your interest in the first place.

"I found another piece of code and it works. The code below does the job for me."

The Qlik code below fetches documents from the BKPF table. I would like to modify this code to also bring in the LIFNR field from the BSEG table by matching via the BELNR field. Then, I want to join this LIFNR with the LFA1 table to retrieve the corresponding NAME1 and NAME2 fields and combine them as "Vendor Name".
Could you please help me update my code to achieve this?

LET vStartDate = Date(MonthStart(Today()), 'YYYYMMDD');
LET vEndDate = Date(MonthEnd(Today()), 'YYYYMMDD');

// 1) Load all data (including TK)
KONTROLAKARAOGLAN_TEMP:
LOAD
BELNR AS BELGE_NO,
BUKRS AS SIRKET_KODU,
GJAHR AS YIL,
BLDAT,
BUDAT,
BLART AS BELGE_TURU,
USNAM AS OLUSTURAN_KULLANICI,
TCODE AS TRANSAKSIYON_KODU,
AEDAT AS DEGISIKLIK_TARIHI,
XBLNR AS REFERANS,
STBLG AS TERSBELGENO,
CPUDT AS ISLEM_TARIHI
WHERE
BLDAT <> BUDAT
;
SQL SELECT
BELNR,
BUKRS,
GJAHR,
BLDAT,
BLART,
USNAM,
TCODE,
AEDAT,
XBLNR,
STBLG,
CPUDT,
BUDAT
FROM BKPF
WHERE USNAM IN (
'USER1', 'USER2'
)
AND BUKRS = '2000'
AND BLDAT BETWEEN '$(vStartDate)' AND '$(vEndDate)'
AND TCODE NOT IN (
'KO88', 'ZCO011', 'ZCO007R1', 'MR21', 'AFABN', 'KSS2', 'KSII',
'KON2', 'KO8G', 'KKAO', 'CO88H', 'MR11', 'KSV5', 'KSU5',
'KE27', 'KEU5', 'CJ88', 'CJ8G', 'AFAB', 'KOC4', '/MBIS/ENF_P006', 'CK24'
)
AND TCODE IS NOT NULL
AND TCODE <> ''
AND BLART NOT IN ('XX', 'FX')
AND (
STBLG IS NULL
OR STBLG = ''
)
;

// 2) Get the References (XBLNR) of entries with 'TK' type - these will be excluded
TK_REFERANSLAR:
LOAD DISTINCT
REFERANS AS EXCLUDE_REF
RESIDENT KONTROLAKARAOGLAN_TEMP
WHERE BELGE_TURU = 'TK'
;

// 3) Create the final result table - exclude 'TK' entries and those with the same reference
KONTROLAKARAOGLAN_FINAL:
LOAD
BELGE_NO,
SIRKET_KODU,
YIL,
Date(BLDAT, 'DD.MM.YYYY') AS BELGE_TARIHI,
Date(BUDAT, 'DD.MM.YYYY') AS KAYITTARIH,
BELGE_TURU,
OLUSTURAN_KULLANICI,
TRANSAKSIYON_KODU,
DEGISIKLIK_TARIHI,
REFERANS,
TERSBELGENO,
ISLEM_TARIHI
RESIDENT KONTROLAKARAOGLAN_TEMP
WHERE BELGE_TURU <> 'TK'
AND NOT Exists(EXCLUDE_REF, REFERANS)
;

// 4) Drop the temporary tables
DROP TABLE KONTROLAKARAOGLAN_TEMP;
DROP TABLE TK_REFERANSLAR;

[hanna_choi]

HELLO @leylabalse 

How about this ways?

Functions : Not Exists

==== Qlik Sense Data Load Script

==== Visualization

[leylabalse]

Hello @hanna_choi 

Thank you very much for your interest in the first p

If I’m not mistaken, you first write the data into a code block and then apply filtering. I can easily do that with Excel VBA. I don’t want to enter data manually. However, my goal is to retrieve data from SAP tables in the format I want.