topic Re: Showing only first and last questions/answers in App Development

Showing only first and last questions/answers

blpetosa — Sat, 16 Nov 2024 07:16:39 GMT

Hello Everyone,

I am working on a project where I am trying to keep only the first "fail" grade and last "pass" grade for users. I need the output in a Qlik Sense Table visualization. Please refer to the attached document for sample data. This only shows the journey of one user, but there will be many more. Some things to note:

1. There are two test_type's (Pre-1,Post-2). Users can only take the Pre-test once, so I will always keep that regardless of if they pass or fail. The Post-test (type 2) is where I only want the first fail, and final pass(users hardly ever take again after passing, but it is possible). Makes me think I may need an "if(test_type=2,?,?)" logic for this field, and both the date and time fields need to be referenced (min/max/firstsortedvalue???).

2. The desired output for this example would be rows:

2,3,4,5,6,7,14,15,16

Thank you in advance for any help,

Bob

Re: Showing only first and last questions/answers

lblumenfeld — Sat, 17 Nov 2018 06:45:37 GMT

The best practice is to prepare this information in your load script, rather than calculate it on-the-fly when you need it. The script below uses your spreadsheet to load a table with the data. It also adds two fields; a test_id that uniquely identifies each test; and a flag called "report" at the end of the record. This flag will be 1 for each question of each test that fits your criteria, otherwise it will be null.

This gives you a lot more flexibility. You can just display the each question or test_id for a user where the report_flag = 1. You also can reduce your data, keeping only the records where report_flag = 1. I think this option is closer to that you want.

The script is below. If you run it you will see that the records you wanted have report_flag = 1. The ones you don't want have report_flag = null. To reduce the data, simply load the table to another table "where report_flag = 1".

I've also attached a spreadsheet with the contents of the Scores table after you run this script.

I hope this solves your issue.

// First load in the spreadsheet.

[RawScores]:

LOAD

[user],

[date],

[time],

[test_type],

[question_order],

[question],

[answer],

[pass_fail]

FROM [lib://Data/Test Example.xlsx] // Change to be your data file to load

(ooxml, embedded labels, table is Sheet1);

//Now, loop through the table you loaded and add a unique test_id for each test.

Let vNumRows = NoOfRows('RawScores');

Let vKey = 0;

Set vConcatenate = 'NoConcatenate';

For i = 0 to $(vNumRows)-1

If Peek('question_order', i, 'RawScores') = 0 then

Let vKey = vKey + 1;

End If

Scores:

$(vConcatenate) LOAD

$(vKey) as test_id,

Peek('user', $(i), 'RawScores') as [user],

Peek('date', $(i), 'RawScores') as [date],

Peek('time', $(i), 'RawScores') as [time],

Peek('test_type', $(i), 'RawScores') as [test_type],

Peek('question_order', $(i), 'RawScores') as [question_order],

Peek('question', $(i), 'RawScores') as [question],

Peek('answer', $(i), 'RawScores') as [answer],

Peek('pass_fail', $(i), 'RawScores') as [pass_fail]

AutoGenerate 1;

Set vConcatenate = 'Concatenate';

Next;

Drop Table [RawScores];

// Identify the pre-test records

[ScoreStatus]:

Load

Max(test_id) as test_id

Resident [Scores]

Where [test_type] = 1

Group by [user];

// Identify the last test that the user passed.

[ScoreStatus]:

Concatenate Load

Max(test_id) as test_id

Resident [Scores]

Where [pass_fail] = 'pass' and [test_type] = 2

Group by [user];

// Identify the first test that the user failed

[ScoreStatus]:

Concatenate Load

Min(test_id) as test_id

Resident [Scores]

Where [pass_fail] = 'fail' and [test_type] = 2

Group by [user];

// Now, add the report_flag to table Scores.

Scores:

Left Join (Scores)

Load

test_id,

1 as report_flag

Resident [ScoreStatus];

Drop Table [ScoreStatus];

Re: Showing only first and last questions/answers

lblumenfeld — Sat, 17 Nov 2018 06:29:47 GMT

Note: In the code in my previous response, the Qlik editor will flag the code "$(vConcatenate) LOAD" as an error. You can ignore that. It's okay.

Re: Showing only first and last questions/answers

blpetosa — Mon, 19 Nov 2018 21:04:22 GMT

Thank you very much for this! I almost have it working for my actual data set, which is a little more complex. The important thing is that I see what you did and understand it. I plan on using this for other projects going forward.

Thanks again,

Bob

Re: Showing only first and last questions/answers

lblumenfeld — Mon, 19 Nov 2018 21:23:01 GMT

You're very welcome. Post again if you run into any other issues.