topic Re: Filling the blanks in QlikView

Filling the blanks

Anonymous — Mon, 01 Oct 2018 12:50:23 GMT

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

Re: Filling the blanks

sunny_talwar — Mon, 01 Oct 2018 16:27:31 GMT

Which of the two instance of a repeating is flagged with the question mark? In case of Paris, you marked the second occurrence with the question mark... but for Boston, it was the first occurrence? How was this decided?

Re: Filling the blanks

Anonymous — Tue, 02 Oct 2018 04:21:02 GMT

Thing is the order is random

When I have duplicates

- one amoung them can be valid (pass the "is even" test)

- It can be any of them

- it can also be none of them

So my test should go like:

That is why I imagine a test going like

- if I give the value 1 to any valid entry

- if I sum the value of the valid field for each category

-> then is that value is <>0, the category is "category valid" then anymember of this category is "valid"

Re: Filling the blanks

sunny_talwar — Tue, 02 Oct 2018 11:06:45 GMT

I guess randomness is okay, but who do you want to code this randomness is the question... I guess you tried to explain this here

So my test should go like:

That is why I imagine a test going like

- if I give the value 1 to any valid entry

- if I sum the value of the valid field for each category

-> then is that value is <>0, the category is "category valid" then anymember of this category is "valid"

But I don't think I am able to understand how is question mark linked to valid or invalid. I think can you provide more data points with the expected output for TestResult to see if I can generalize what you are trying to do?

Re: Filling the blanks

Anonymous — Tue, 02 Oct 2018 11:32:54 GMT

Thank you for trying Sunny

If you look at the last table on the original question, you see:

- 2 out of the 6 entries lack a CODE. Namely entry 3 and entry 4

This lead to the same entries lacking a TestResult value (?)

But we know that enry 3 belong has the value "BonstonCAT" in category

And this is my knowledge that entries of the same Category have CODE of the same king (even or odd)

=> some I know that entry 3 should have a CODE of the same kind as entry 6 (odd)

therefore its TestResult is 0

Same goes for entry 4 that should have 1 in TestResult because, even though it doesn't have CODE value,

it is of Category "ParisCAT" ...And we already have one entry of Category "ParisCAT", its TestResult is 1, therefore it is an even number

For every Category there is alway 1 entry with a CODE value

Does that helps ?

Re: Filling the blanks

jensmunnichs — Tue, 02 Oct 2018 11:34:45 GMT

I don't think I fully understand your question and I might be oversimplifying, but

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

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

Can you not just add an IF statement to your 'test' that returns 0 when the code is missing?

Something like IF(IsNull(Code), 0, YourTest)

Re: Filling the blanks

Anonymous — Tue, 02 Oct 2018 11:43:39 GMT

I guess

IF(IsNull(Code), 0, YourTest)

wouldn't work because I cannot run the test on the entry that has isNull(Code)

What I need is to ask:

If isNull(Code) then take the TestResult of the Caterory

That is why I'm thinking about creating another table with 2 fields

Category | CategoryTestResult

And to make the CategoryTestResult value to True (or 1) if at least 1 entry of the same category has

TestResult = 1

Re: Filling the blanks

jensmunnichs — Tue, 02 Oct 2018 11:56:10 GMT

I think I'm starting to understand.

Do you already know all the codes and their corresponding values? In that case you could create a mapping table and apply it to the data to make sure none of the codes are missing.

There might be a better solution though, I'll let you know if I think of it.

Re: Filling the blanks

Anonymous — Tue, 02 Oct 2018 11:59:41 GMT

almost that

I don't know all the codes

some entries have no CODE value

What I know is:

In every Category, there is at least 1 Entry that has a CODE value

So I'm sure that I could deduce the TestResult of every Entry

Re: Filling the blanks

jensmunnichs — Tue, 02 Oct 2018 12:15:56 GMT

What about this:

Sort the original table by Value, then Code ascending. Then load code by checking whether there is a code or not. If there is, use that code. If not, use the code from the previous row. That way every value should have a code and the test should work for every entry, correct? Then if necessary sort by ID again.

The only situation I can think of where this wouldn't work is if there is a category where no entry has a code, but as you said there should always be at least one.

I've attached a file where I made this work, note that when there is no code my table shows '?', you might have to change that if it's different in your data. Just make sure that the 'order by' always puts the rows with a code above the ones without a code, or this won't work.

Hope this helps, I'm still fairly new to Qlikview myself so there might still be a better solution to your problem.

Re: Filling the blanks

Anonymous — Tue, 02 Oct 2018 12:30:35 GMT

it seems great !

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

thank you

Re: Filling the blanks

rwunderlich — Tue, 02 Oct 2018 15:34:14 GMT

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

Re: Filling the blanks

Anonymous — Wed, 03 Oct 2018 12:12:16 GMT

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 !