Filling the blanks

[Anonymous]

Hi there

here is what I'm trying to achieve

My entries have a code, and some codes are missing

I use the code to test the entry (let's say the test is "is the code an even number ?")

When the code is missing, I know I can use the result of the test from another entry with the same value

it look like this:

ID	VALUE	CODE
1	Paris	2
2	Denver	5
3	Boston	...
4	Paris	...
5	Seattle	6
6	Boston	3

My first step is to add a field to Flag the duplicate Values

then it looks like this

ID	VALUE	CODE	Duplicate
1	Paris	2	YES
2	Denver	5	NO
3	Boston	...	YES
4	Paris	...	YES
5	Seattle	6	NO
6	Boston	3	YES

Then I create a category field to group the entries with duplicate & the same value

ID	VALUE	CODE	Duplicate	Category
1	Paris	2	YES	ParisCAT
2	Denver	5	NO	0
3	Boston	...	YES	BostonCAT
4	Paris	...	YES	ParisCat
5	Seattle	6	NO	0
6	Boston	3	YES	BostonCAT

Now I run the Test and put the result ina new Field

ID	VALUE	CODE	Duplicate	Category	TestResult
1	Paris	2	YES	ParisCAT	1
2	Denver	5	NO	0	0
3	Boston	...	YES	BostonCAT	?
4	Paris	...	YES	ParisCAT	?
5	Seattle	6	NO	0	1
6	Boston	3	YES	BonstonCAT	0

Now my question is

How do I get to to create a fill the cells with the Question mark ?

I started creating a Category: table where I list all categories and I am trying to add a field with a value that says

If the Somme of the test result for 1 category is >= 1, then the category should be flagged as Valide

But I can't work out the bit of script that does that

or maybe there is a way to fill the "test result" field directly

anyone ?

cheers

ps: just so you know I stated with the Udemy course but I need to get to solve this now

[Anonymous]

it seems great !

I'm taking it home to work at it tonight and I'll tell you tomorow

thank you

[rwunderlich · Accepted solution]

How about:

Raw:

LOAD ID,

    VALUE,

    CODE,

    if(even(CODE), 1, 0) as TestResultTemp

FROM

[https://community.qlik.com/thread/315510]

(html, codepage is 1252, embedded labels, table is @1);

LEFT JOIN (Raw)

LOAD

VALUE,

max(TestResultTemp) as TestResult

Resident Raw

Group By VALUE

;

DROP FIELD TestResultTemp;

-Rob

http://masterssummit.com

http://qlikviewcookbook.com

http://www.easyqlik.com

[Anonymous]

And ... Yes !

Thank you very much Rob

This is exactly what I was looking for

Jens's answer worked for the exemple but the LEFT JOIN / Group by
solution works alway

You guys made a very happy guy in Reunion Island , thank you !