topic Re: Set Flag based on a value x days ago in QlikView

Set Flag based on a value x days ago

Milo2009 — Wed, 20 Jan 2016 15:40:23 GMT

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:

DATE	ARTNR	VALUE	FLAG
03.01.2016	1000
10.01.2016	3333
10.01.2016	1000	WP	WP
10.01.2016	2000	ZP	ZP
17.01.2016	1000		WP
17.01.2016	2000		ZP
17.01.2016	3333	WP	WP

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'.

Re: Set Flag based on a value x days ago

Wed, 20 Jan 2016 17:05:23 GMT

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.

Re: Set Flag based on a value x days ago

swuehl — Wed, 20 Jan 2016 17:15:52 GMT

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;

Re: Set Flag based on a value x days ago

Milo2009 — Wed, 20 Jan 2016 17:47:51 GMT

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

Re: Set Flag based on a value x days ago

MarcoWedel — Wed, 20 Jan 2016 21:30:24 GMT

Hi,

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

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

Re: Set Flag based on a value x days ago

Milo2009 — Fri, 22 Jan 2016 16:48:26 GMT

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

Re: Set Flag based on a value x days ago

Milo2009 — Fri, 22 Jan 2016 16:51:55 GMT

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