topic How to define the below Logic in Qlikview Load Script in QlikView

How to define the below Logic in Qlikview Load Script

psk180590 — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: How to define the below Logic in Qlikview Load Script

Digvijay_Singh — Tue, 24 Apr 2018 16:24:50 GMT

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))

));

Re: How to define the below Logic in Qlikview Load Script

YoussefBelloum — Tue, 24 Apr 2018 16:41:52 GMT

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

Re: How to define the below Logic in Qlikview Load Script

MarcoWedel — Tue, 24 Apr 2018 16:46:25 GMT

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

Re: How to define the below Logic in Qlikview Load Script

shiveshsingh — Tue, 24 Apr 2018 16:47:13 GMT

You can define null field as zero, put condition accordingly while load script

Re: How to define the below Logic in Qlikview Load Script

psk180590 — Tue, 24 Apr 2018 16:50:30 GMT

Hello All,

Just to be more clear, have attached sample data and the expected result

Re: How to define the below Logic in Qlikview Load Script

psk180590 — Tue, 24 Apr 2018 17:05:22 GMT

Corrected the data, this one is more what i'm looking at.

Re: How to define the below Logic in Qlikview Load Script

sasiparupudi1 — Tue, 24 Apr 2018 18:39:30 GMT

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

Re: How to define the below Logic in Qlikview Load Script

psk180590 — Tue, 24 Apr 2018 19:16:01 GMT

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).