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.
sebmueller87
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 advanceSmiley Happy

Kind Regards

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

1 Solution

Accepted Solutions

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

6 Replies
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.

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;

sebmueller87
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 possibleSmiley Happy Thanks in advanceSmiley Happy

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

sebmueller87
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)?

sebmueller87
New Contributor II

Re: Set Flag based on a value x days ago

Hey dathu.qv,

thanks for your replySmiley Happy 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 fineSmiley Happy

Community Browser