Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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'.
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
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.
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;
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:)
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
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)?
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:)