Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
Make your voice heard! Participate in the 2020 Wisdom of Crowds® Survey. BEGIN SURVEY
Highlighted
Milo2009
New Contributor II

Set Flag based on a value x days ago

Hey everybody,

the following is the initial situation. Every day QlikView is fetching new Data from a Database. The whole dataset in the QVW contains about 45.000.000 rows. So every day there will be added about 60.000 rows.

What I want to do is the following: Based on a value 7 days ago or the actual day (when the script runs), I want to set a Flag in the new records. The table illustrates my problem, the green rows are the old data and the other rows are the records I'm adding to my dataset:

DATEARTNRVALUEFLAG
03.01.20161000
10.01.20163333
10.01.20161000WPWP
10.01.20162000ZPZP
17.01.20161000WP
17.01.20162000ZP
17.01.20163333WPWP

So when I add new records I want QV to look 7 days back in the field 'VALUE' for the certain product and set a 'X' in the field 'FLAG', if the field 'VALUE' contains a 'WP' (yellow cells). Also I want so set a 'X' when there is just in the new record a 'WP' for the certain product (blue cells).

I could use some kind of Lookup to get the the content of the field 'VALUE', but it's taking too much time at all.

So do have any ideas how I could face that problem in an appropriate script runtime?

Thanks a lot in advance:)

Kind Regards

Nachricht geändert: I've edited the table content for the field 'FLAG'.

1 Solution

Accepted Solutions
Highlighted

Re: Set Flag based on a value x days ago

Hi,

to create the flag for historical occurrences of "WP" you could use something like:

QlikCommunity_Thread_200779_Pic1.JPG

table1:

LOAD DATE,

    ARTNR,

    VALUE

FROM [https://community.qlik.com/thread/200779] (html, codepage is 1252, embedded labels, table is @1);

Left Join (table1)

LOAD DATE+7 as DATE,

    ARTNR,

    'X' as FLAG

Resident table1

Where VALUE='WP';

hope this helps

regards

Marco

View solution in original post

6 Replies
Highlighted
Not applicable

Re: Set Flag based on a value x days ago

You may try with Mapping table & ApplyMap

Create the mapping table:

Map_Flag:

Mapping

Load * INLINE [

Date&'-'Value , Flag

<use logic to generate the dates>& '-' & 'WP' , 1

];

And use ApplyMap function to add the Flag to your fact table. You can add on the qvd level so it will not impact any performance.

Highlighted
MVP
MVP

Re: Set Flag based on a value x days ago

How are you performing the lookup?

You could do it using EXISTS() function, using something like this


LOAD DATE,
     ARTNR,
     VALUE,
//     FLAG
  If(VALUE = 'WP',Num(DATE) & '-' & ARTNR) as Lookup,
  If(VALUE = 'WP' OR Exists(Lookup, Num(DATE)-7 & '-' &ARTNR),'X') as FLAG
FROM
[https://community.qlik.com/thread/200779]
(html, codepage is 1252, embedded labels, table is @1);

DROP FIELD Lookup;

Milo2009
New Contributor II

Re: Set Flag based on a value x days ago

Thats sounds as a nice opportunity. With Lookup I meant the use like this:

Lookup('VALUE','LOOKUP', NUM(DATE)-7 &'-'& ARTNR, TABLE)

But I will try your idea as soon as possible:) Thanks in advance:)

Highlighted

Re: Set Flag based on a value x days ago

Hi,

to create the flag for historical occurrences of "WP" you could use something like:

QlikCommunity_Thread_200779_Pic1.JPG

table1:

LOAD DATE,

    ARTNR,

    VALUE

FROM [https://community.qlik.com/thread/200779] (html, codepage is 1252, embedded labels, table is @1);

Left Join (table1)

LOAD DATE+7 as DATE,

    ARTNR,

    'X' as FLAG

Resident table1

Where VALUE='WP';

hope this helps

regards

Marco

View solution in original post

Highlighted
Milo2009
New Contributor II

Re: Set Flag based on a value x days ago

swuehl I've tested your idea and it works very good

For now I just can add a static 'X' as a Flag or take the content of the field 'value', when I'm just 'in the' specific row (f. ex. row 3 and 4, where I have  different flags). For this I perform a left join. How can I adjust this approach in order to get the content of the field 'value' also in the field 'Flag' in row 5 and 6 (the output should look like the table - I've adjusted the table)?

Highlighted
Milo2009
New Contributor II

Re: Set Flag based on a value x days ago

Hey dathu.qv,

thanks for your reply:) I've also tested this approach and it seems to be faster than creating a lookup field and using exists.

By the way, what do you mean with "adding on the qvd level"? For now, I load the fact table from the qvd. After that I create the mapping table with a resident load of the fact table and perform at the end a left join with ApplyMap. That works fine:)