Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello All,
I have a Table with the following Details:
Raw Data:
QuestionNumber | AnswerNumber | AnswerText |
15 | 1 | 0 |
15 | 1 | 1 |
140 | 20 | 2 |
150 | 30 | 2 |
160 | 30 | 3 |
Expected Result:
QuestionNumber | AnswerNumber | AnswerText |
15 | 1 | 0 |
140 | 1 | 0 |
150 | 1 | 0 |
160 | 1 | 0 |
15 | 1 | 1 |
140 | 20 | 2 |
150 | 30 | 2 |
160 | 30 | 3 |
Basically, If the QNo 15 has ANo 1 and all the other QNo have no entries(or Null Values) i should fill up 0 against the AnswerNumber and AnswerText. If there are Values against each QNO, they should remain as is.
Note: This Should be generated at the script level as i have to further use this Table.
TIA!!
Nachricht geändert durch Pakalapati Sai Kumar
You may try 'fill' in transformation steps while loading the table. The other way could be using previous function with some condition through resident load.
LOAD QuestionNumber,
AnswerNumber,
AnswerText
FROM
[https://community.qlik.com/thread/299416]
(html, codepage is 1252, embedded labels, table is @1, filters(
Replace(2, top, StrCnd(null)),
Replace(3, top, StrCnd(null))
));
Hi,
try this code:
RawData:
LOAD QuestionNumber,
if(len(trim(AnswerNumber))=0, 'fill_zero',AnswerNumber) as AnswerNumber,
if(len(trim(AnswerText))=0, 'fill_zero',AnswerText) as AnswerText
FROM
[https://community.qlik.com/thread/299416]
(html, codepage is 1252, embedded labels, table is @1);
with this, you will be able to see the difference between the original 0 and the cells that you fill in the script.
if it is ok for you, just replace the 'fill_zero' with 0
Hi,
maybe one solution could be:
table1:
LOAD QuestionNumber,
If(Len(Trim(AnswerNumber)),AnswerNumber,Peek(AnswerNumber)) as AnswerNumber,
If(Len(Trim(AnswerText)),AnswerText,Peek(AnswerText)) as AnswerText
FROM [https://community.qlik.com/thread/299416] (html, codepage is 1252, embedded labels, table is @1);
hope this helps
regards
Marco
You can define null field as zero, put condition accordingly while load script
Hello All,
Just to be more clear, have attached sample data and the expected result
Corrected the data, this one is more what i'm looking at.
why the rows are not repeated for the example 3?
QuestionNumber | AnswerNumber | AnswerText |
Example1: | ||
15 | 1 | 0 |
140 | 0 | 0 |
150 | 0 | 0 |
160 | 0 | 0 |
Example 2: | ||
15 | 1 | 1 |
140 | 20 | 2 |
150 | 30 | 2 |
160 | 30 | 3 |
Example 3: | ||
15 | 0 | 0 |
Let me explain the question a little bit more
Say, there is a Survey where QNo 1 has to be answered with Two Options 1 or 2. When the User selects 2, they are allowed to Answer further Questions like QNo 2 with Answer 3,4,5.....
But, if the User Selects Answer 1 for QNo1, the survey ends with only one Answer.
Now, here i'm looking to populate all the other QNo's with the same answer as for QNo(1 in this case).