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