Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Milo2009
Contributor II
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
MarcoWedel

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
Not applicable

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.

swuehl
MVP
MVP

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
Contributor II
Contributor II
Author

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

MarcoWedel

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

Milo2009
Contributor II
Contributor II
Author

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

Milo2009
Contributor II
Contributor II
Author

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