Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Blacklist implementation in script

Hello experts,

I need to implement a kind of “blackist” with some logic behind.

I’ve already tried some stuff, but it doesn’t work as required still.

The requirement:

We have a SalesData table and a Blacklist table.

Screenshot03-Results.png

If a customer is in the blacklist table, we need to check the year & month since he is blacklisted. From that date on all of his records should be removed from the table. It must be done in script, playing with the filters is not an option ; )


E.g. 101 John Smith is blacklisted since 02-2015. All records after 02-2015 will be removed from the table. Only the record from 01-2015 will stay, because he was not blacklisted on that date. The customer 105 Joshua Sleedge will be removed, too.

I’ve setup an example with all data already in place, so you can play with it.

In the file “01_HowTo_Blacklist-data_load_only.qvw” the data is just loaded. – nothing to do there

In the file “02_HowTo_Blacklist-data_modelling.qvw” we should remove the blacklisted customers. In the load script, if possible (but without playing with filters)

Thanks

Nik

1 Solution

Accepted Solutions
puttemans
Valued Contributor

Re: Blacklist implementation in script

Hi Nik,

Please try like below.

MAP_blacklist:

MAPPING LOAD

  CustomerID,

     //CustomerName,

     Year&Month as date

FROM

(ooxml, embedded labels, table is Tab1);

100:

LOAD Team,

     CustomerID,

     CustomerName,

     Category,

     Product,

     Year,

     Month,

     Sales,

     Profit,

     Applymap('MAP_blacklist',CustomerID, 202012) as dateblacklisted

FROM

(ooxml, embedded labels, table is Tab1);

NoConcatenate

200:

LOAD *

RESIDENT 100

WHERE Year&Month < dateblacklisted;

DROP TABLE 100;

Kind regards,

Johan

3 Replies
puttemans
Valued Contributor

Re: Blacklist implementation in script

Hi Nik,

Please try like below.

MAP_blacklist:

MAPPING LOAD

  CustomerID,

     //CustomerName,

     Year&Month as date

FROM

(ooxml, embedded labels, table is Tab1);

100:

LOAD Team,

     CustomerID,

     CustomerName,

     Category,

     Product,

     Year,

     Month,

     Sales,

     Profit,

     Applymap('MAP_blacklist',CustomerID, 202012) as dateblacklisted

FROM

(ooxml, embedded labels, table is Tab1);

NoConcatenate

200:

LOAD *

RESIDENT 100

WHERE Year&Month < dateblacklisted;

DROP TABLE 100;

Kind regards,

Johan

MVP
MVP

Re: Blacklist implementation in script

Or maybe using a common key

Temp:

LOAD * FROM SalesData;

LEFT JOIN (Temp)

LOAD Blacklist_CustomerID as SalesData_CustomerID,

           Blacklist_Year,

           Blacklist_Month

FROM Blacklist;


Result:

NOCONCATENATE

LOAD *

RESIDENT Temp

WHERE MakeDate(SalesData_Year,SalesData_Month) < RangeMin(MakeDate(Blacklist_Year,Blacklist_Month), Today(1));

DROP Table Temp;

edit: RangeMin...

Not applicable

Re: Blacklist implementation in script

thank you very much, Johan.

The answer of swuehl was also helpfull.

I've uploaded the working solution for the community.

Community Browser