Quiz Sense – Who wants to be a Qlikionnaire?

Hey everyone, for my inaugural post on this blog I want to introduce you to an idea I had at the beginning of last week: Quiz Sense. It’s a pun of Qlik Sense and – well – quiz. It’s a Telegram and Qlik Sense based game that connects to your Sense Server and then generates Questions based on master dimensions and measures from a specific app. It gives players 3 lives to answer these questions correctly and awards scorestreaks for successfully answering successive questions. You can try it here and download the application here.

As a professional being passionate about data, technology and Qlik’s products I love to play and experiment with the platform. Another technology that I love to tinker with are lingual interfaces – especially chatbots. Everyone uses a smartphone these days and an interface that allows you to just ask a question and get the answer is one of the most natural ways to interact with and obtain information. Another more business focussed culmination of this would be an experimental alerting solution I built.

No one – that I know off – has built a Quiz based on business data yet though. So here’s Quiz Sense

Quiz Sense… uh… BUT WHY??!

My standard answer to this question normally is: “Why not?!”, but in this case I’ll give you a few points as to what I consider could be a use for this:

Gamification is a grand way to engage people with things they may be less passionate about. By giving out a quiz that asks players questions about who is the biggest customer, what was the conversion rate in a specific year or similar may encourage them to more closely look at numbers, names and facts and therefore make them better acquainted with data in their environment
Questions that a line manager might ask “What was last years revenue in South Carolina?” or “What was the maximum freight for our Costumer Bob’s Building Supplies?” can be answered rapidly if you have software like Qlik Sense. So engaging people unfamiliar with Qlik Software with Quiz Sense and giving them the Qlik Sense app on a screen next to it will show them how easy one can find these answers if they have the right tool.

There might be more… if you have ideas, I’ll be happy to hear about them in the comments or via Social Media. For me however “Why not?” is enough for now ;-).

OK,I get it – I think – so how does this work?

Quiz Sense is made up of four main components:

Game and Database (game logic and information handling)
Bot (Telegram handlers)
Qlik Sense Interface (enigma.js methods)

The entire code is written in typescript and runs on node.js. To run your own copy do the following:

Download the repo (see above)
Extract and run npm install from inside the main directory (where package.json is)
Enter the configuration for your telegram bot and Qlik Sense connection in ./config/config.ts
Run tsc in the main directory to compile your code.
Run node dist/app.js

////config.ts

// Telegram stuff
export var botToken: string = '&lt;YOUR TELEGRAM API TOKEN>';

// Environment stuff
export var proxy: string = '&lt;YOUR HTTP PROXY ADDRESS | null if unapplicable>';

// Qlik Sense Stuff
export var qlikCertificateDir: string = './src/cert/';
export var qlikUser: string     = 'UserDirectory= INTERNAL;UserId= SA_API';
export var qlikServer: string   = '&lt;YOUR QS SERVER WSS URL | ws://localhost:9076/app/engineData for QS Desktop>';
export var qlikApp: string      = '&lt;THE APP ID YOU WANT TO CONNECT TO>';

////config.ts

// Telegram stuff

export var botToken: string = '<YOUR TELEGRAM API TOKEN>';

// Environment stuff

export var proxy: string = '<YOUR HTTP PROXY ADDRESS | null if unapplicable>';

// Qlik Sense Stuff

export var qlikCertificateDir: string = './src/cert/';

export var qlikUser: string = 'UserDirectory= INTERNAL;UserId= SA_API';

export var qlikServer: string = '<YOUR QS SERVER WSS URL | ws://localhost:9076/app/engineData for QS Desktop>';

export var qlikApp: string = '<THE APP ID YOU WANT TO CONNECT TO>';

If you run this in a Qlik Sense Server environment, make sure to export the appropriate Certificates from your server and uncomment the certificate loader in ./qs/qs.ts.

Before I run you through the application, here’s a disclaimer: Obviously, anything you do, you do at your own risk.

Here’s an architectural overview of the app:

and here’s the story:

Once you start the game Quiz Sense will connect to the Qlik Sense app specified in the config using enigma.js and pull all master dimensions and measures from the app. It will then randomly select one of each and create a hypercube session object for this combination. Upon receiving the hypercube’s layout a random value within the cube will be a selected and the information returned.

//// qs.ts

// ...

export function qsPulldata () {

    const session = enigma.create({
        schema,
        url: config.qlikServer,
        createSocket: url => new webSocket(url,{
            // ca: root, // Uncomment when on Server
            // key: key, // Uncomment when on Server
            // cert: client, // Uncomment when on Server
            headers: {
                'X-Qlik-User': config.qlikUser
            },
        })
    });

    return new Promise((resolve, reject) => {
        session.open()
        .then((global) => {
            return global.openDoc(config.qlikApp)
        }).then((app) => {

            //Get Measures
            var p1 = new Promise((resolve, reject) => {
                app.createSessionObject(myCubes.msre).then((obj) => {
    
                    obj.getLayout().then( (layout) => {
                        let idx = Math.ceil(Math.random()*layout.qMeasureList.qItems.length) - 1;
                        resolve([layout.qMeasureList.qItems[idx].qMeta.title.replace('*', ' ').replace('_', ' ').replace('~', ' '), layout.qMeasureList.qItems[idx].qInfo.qId]);
                    }).catch(err => console.log('Rejection: ', err))
    
                }).catch(err => console.log('Rejection: ', err));
    
            }).catch(err => console.log('Rejection: ', err));
    
            //Get Dimesnions
            var p2 = new Promise( (resolve, reject) => {
                app.createSessionObject(myCubes.dims).then((obj) => {
    
                    obj.getLayout().then( (layout) => {
                        let idx = Math.ceil(Math.random()*layout.qDimensionList.qItems.length) - 1;
                        resolve([layout.qDimensionList.qItems[idx].qMeta.title.replace('*', ' ').replace('_', ' ').replace('~', ' '), layout.qDimensionList.qItems[idx].qInfo.qId]);
    
                    }).catch(err => console.log('Rejection: ', err));
    
                }).catch(err => console.log('Rejection: ', err));
    
            }).catch(err => console.log('Rejection: ', err));
            
            //Pass through app object
            var p3 = Promise.resolve(app);
    
            return Promise.all([p1, p2, p3])
        
        }).then((r) => {
                var cResults = r.slice(0,2);
                var app = r[2];
                console.log(cResults);
                
                let hyperCubeDef = myCubes.buildHyperCube(cResults[1][1], cResults[0][1]);
    
                return new Promise((resolve, reject) => {
                    app.createSessionObject(hyperCubeDef).then((obj) => {
                        return obj.getLayout()
                    }).then((layout) => {
                        let idx = Math.round(layout.qHyperCube.qDataPages[0].qMatrix.length * Math.random());
                        cResults.push([layout.qHyperCube.qDataPages[0].qMatrix[idx][0].qText.replace('*', ' ').replace('_', ' ').replace('~', ' '), layout.qHyperCube.qDataPages[0].qMatrix[idx][1].qText])
                        resolve(cResults);
                    })            
                })
                
        }).then((results) => {
            console.log('results: ' + JSON.stringify(results));
            session.close();
            resolve(results);
        })
        .catch(err => console.log('Rejection: ', err))
    })
};

//// qs.ts

// ...

export function qsPulldata () {

const session = enigma.create({

schema,

url: config.qlikServer,

createSocket: url => new webSocket(url,{

// ca: root, // Uncomment when on Server

// key: key, // Uncomment when on Server

// cert: client, // Uncomment when on Server

headers: {

'X-Qlik-User': config.qlikUser

})

});

return new Promise((resolve, reject) => {

session.open()

.then((global) => {

return global.openDoc(config.qlikApp)

}).then((app) => {

//Get Measures

var p1 = new Promise((resolve, reject) => {

app.createSessionObject(myCubes.msre).then((obj) => {

obj.getLayout().then( (layout) => {

let idx = Math.ceil(Math.random()*layout.qMeasureList.qItems.length) - 1;

resolve([layout.qMeasureList.qItems[idx].qMeta.title.replace('*', ' ').replace('_', ' ').replace('~', ' '), layout.qMeasureList.qItems[idx].qInfo.qId]);

}).catch(err => console.log('Rejection: ', err))

}).catch(err => console.log('Rejection: ', err));

//Get Dimesnions

var p2 = new Promise( (resolve, reject) => {

app.createSessionObject(myCubes.dims).then((obj) => {

obj.getLayout().then( (layout) => {

let idx = Math.ceil(Math.random()*layout.qDimensionList.qItems.length) - 1;

resolve([layout.qDimensionList.qItems[idx].qMeta.title.replace('*', ' ').replace('_', ' ').replace('~', ' '), layout.qDimensionList.qItems[idx].qInfo.qId]);

}).catch(err => console.log('Rejection: ', err));

//Pass through app object

var p3 = Promise.resolve(app);

return Promise.all([p1, p2, p3])

}).then((r) => {

var cResults = r.slice(0,2);

var app = r[2];

console.log(cResults);

let hyperCubeDef = myCubes.buildHyperCube(cResults[1][1], cResults[0][1]);

return new Promise((resolve, reject) => {

app.createSessionObject(hyperCubeDef).then((obj) => {

return obj.getLayout()

}).then((layout) => {

let idx = Math.round(layout.qHyperCube.qDataPages[0].qMatrix.length * Math.random());

cResults.push([layout.qHyperCube.qDataPages[0].qMatrix[idx][0].qText.replace('*', ' ').replace('_', ' ').replace('~', ' '), layout.qHyperCube.qDataPages[0].qMatrix[idx][1].qText])

resolve(cResults);

})

}).then((results) => {

console.log('results: ' + JSON.stringify(results));

session.close();

resolve(results);

})

.catch(err => console.log('Rejection: ', err))

})

};

This data is then taken and converted into a question: One of 3 options is selected at random (A, B or C) and the correct value is assigned. The game then generates 2 wrong options, distributes them to the remaining letters and ultimately passes the question on to the chatbot. The last step in question generation, is saving the question to the database and wait for the answer.

//// game.ts

// ...

export function newQuestion (chat_id, gid) {
    console.log('Sending new Question')

    //Get stuff from engine here
    qs.qsPulldata()
    .then((data) => {
        let val_cor: number = data[2][1];
        let op1: number     = data[2][1]*Math.random();
        let op2: number     = data[2][1]*Math.random();

        let answer_array = ['A', 'B', 'C'];

        let question_array = [];

        let answer_corr = answer_array[Math.round(2*Math.random())];

        switch(answer_corr) {
            case 'A':
                question_array.push(val_cor);
                var answer_1 = 'B';
                question_array.push(op1);
                var answer_2 = 'C';
                question_array.push(op2);
                break;
            
            case 'B':
                var answer_1 = 'A';
                question_array.push(op1);
                question_array.push(val_cor);
                var answer_2 = 'C';
                question_array.push(op2);
                break;
            
            case 'C':
                var answer_1 = 'A';
                question_array.push(op1);
                var answer_2 = 'B';
                question_array.push(op2);
                question_array.push(val_cor);
                break;
        }

        let answerString = answer_corr + ' - ' + val_cor + ';' + answer_1 + ' - ' + op1 + ';' +answer_2 + ' - ' + op2;

        db.insertQuestion(gid, answerString, answer_corr, data[1][0], data[0][0]).then((qid) => {
            console.log('qid: ' + qid)
            bot.sendQ(chat_id, qid, 'What do you think is the ' + data[0][0].substring(0, 25) + ' per ' + data[1][0].substring(0, 25) + ' for ' + data[2][0].substring(0, 25) + '?', parseFloat(question_array[0]).toFixed(2), parseFloat(question_array[1]).toFixed(2), parseFloat(question_array[2]).toFixed(2));
        });
    })    
}

//// game.ts

// ...

export function newQuestion (chat_id, gid) {

console.log('Sending new Question')

//Get stuff from engine here

qs.qsPulldata()

.then((data) => {

let val_cor: number = data[2][1];

let op1: number = data[2][1]*Math.random();

let op2: number = data[2][1]*Math.random();

let answer_array = ['A', 'B', 'C'];

let question_array = [];

let answer_corr = answer_array[Math.round(2*Math.random())];

switch(answer_corr) {

case 'A':

question_array.push(val_cor);

var answer_1 = 'B';

question_array.push(op1);

var answer_2 = 'C';

question_array.push(op2);

break;

case 'B':

var answer_1 = 'A';

question_array.push(op1);

question_array.push(val_cor);

var answer_2 = 'C';

question_array.push(op2);

break;

case 'C':

var answer_1 = 'A';

question_array.push(op1);

var answer_2 = 'B';

question_array.push(op2);

question_array.push(val_cor);

break;

}

let answerString = answer_corr + ' - ' + val_cor + ';' + answer_1 + ' - ' + op1 + ';' +answer_2 + ' - ' + op2;

db.insertQuestion(gid, answerString, answer_corr, data[1][0], data[0][0]).then((qid) => {

console.log('qid: ' + qid)

bot.sendQ(chat_id, qid, 'What do you think is the ' + data[0][0].substring(0, 25) + ' per ' + data[1][0].substring(0, 25) + ' for ' + data[2][0].substring(0, 25) + '?', parseFloat(question_array[0]).toFixed(2), parseFloat(question_array[1]).toFixed(2), parseFloat(question_array[2]).toFixed(2));

});

})

}

The chatbot component is made up of two main parts: One handles messages. This is used to send the initial “New Game” button and also works as a fail safe: should the game get stuck just send a message and you’ll get another „New Game“ Button. It will also log users so that high scores and games can be kept track of.

//// bot.ts 

// ...

bot.on('message', (msg) => {
    var from = msg.from;
    var sender_id = msg.chat.id;

    db.checkUser(sender_id)
    .then((result) => {
        if(result=='no_user') {
            console.log('Unknown User - Creating');
            db.insertUser(from, sender_id).then((result) => {
                console.log(result);
            })
        }
    });

    bot.sendMessage(sender_id, 'Hello ' + from.first_name, {
        parse_mode: 'Markdown',
        reply_markup: {
            inline_keyboard: [[
                {
                text: 'New Game',
                callback_data: 'ng_'
                }
            ]]
            }
    })  
})

//// bot.ts

// ...

bot.on('message', (msg) => {

var from = msg.from;

var sender_id = msg.chat.id;

db.checkUser(sender_id)

.then((result) => {

if(result=='no_user') {

console.log('Unknown User - Creating');

db.insertUser(from, sender_id).then((result) => {

console.log(result);

})

}

});

bot.sendMessage(sender_id, 'Hello ' + from.first_name, {

parse_mode: 'Markdown',

reply_markup: {

inline_keyboard: [[

{

text: 'New Game',

callback_data: 'ng_'

}

]]

}

})

The other, far more important, component is the callback query handler. This element handles New Game requests and – more importantly – question feedback. If a player picks an answer this part of the game will check if that answer was correct. If that is the case, the user will get a score based on the number of consecutive correct answers (for every 3 correct answers one gets 50 more points per correct answer) and is then sent a new question as described above.

Should the answer be wrong the score streak will be knocked down one tier and the player loses a life. Should no lives be left, the game ends, the score gets recorded and the player can start over. If the player has lives left over, he gets a new question.

The event handling here is split between database (db.ts) and bot (bot.ts) methods:

//// bot.ts

// ...

//Callback Handlers
bot.on('callback_query', (data) => {
    var chat_id = data.from.id;
    console.log(data);
    if (data.data.substring(0, 3).toUpperCase() == 'NG_') {
        //new game
        db.newGame(data.from.id)
        .then((game_id) => {
            game.newQuestion(chat_id, game_id)
        })
    } else {
        // received answer
        let qid = data.data.substring(0, data.data.length - 2);
        let reply = data.data.slice(-1);
        let gid = qid.substring(qid.indexOf('_')+1);

        db.checkQuestionActive(qid).then((status:any) => {
            if(status) {
                
                //validate Answer
                db.checkAnswerandUpdate(qid, reply)
                .then((answer: any) => {
                    if (answer[0] == 'true') {
                        console.log('Yay!, score: ' + answer[1]);
                        bot.sendMessage(chat_id, '*Correct!* Your score for this answer: ' + answer[1], {parse_mode: 'Markdown'});                        
                    } else {
                        console.log('Nay');
                        bot.sendMessage(chat_id, 'Your answer was wrong. Your remaining lives: *' + answer[2] +'*', {parse_mode: 'Markdown'})
                    }
                    if (answer[2] > 0) {
                        // new question
                        console.log('Still lives remaining!')
                        game.newQuestion(chat_id, gid);
                    } else {
                        // game over
                        console.log('No lives remaining!')
                        db.gameOver(gid).then((score) => {
                            console.log('final score: ' + score);
                            bot.sendMessage(chat_id, '*GAME OVER* :( your final score was *' + score + '*', {
                                parse_mode: 'Markdown',
                                reply_markup: {
                                    inline_keyboard: [[
                                      {
                                        text: 'New Game',
                                        callback_data: 'ng_'
                                      }
                                    ]]
                                  }
                            })  
                        })
                    }

                })
            }
            else {
                bot.sendMessage(data.from.id, 'This question has *already been answered* or has *expired*.', {parse_mode: 'Markdown'})
            }
        })

        
    }
})

//// bot.ts

// ...

//Callback Handlers

bot.on('callback_query', (data) => {

var chat_id = data.from.id;

console.log(data);

if (data.data.substring(0, 3).toUpperCase() == 'NG_') {

//new game

db.newGame(data.from.id)

.then((game_id) => {

game.newQuestion(chat_id, game_id)

})

} else {

// received answer

let qid = data.data.substring(0, data.data.length - 2);

let reply = data.data.slice(-1);

let gid = qid.substring(qid.indexOf('_')+1);

db.checkQuestionActive(qid).then((status:any) => {

if(status) {

//validate Answer

db.checkAnswerandUpdate(qid, reply)

.then((answer: any) => {

if (answer[0] == 'true') {

console.log('Yay!, score: ' + answer[1]);

bot.sendMessage(chat_id, '*Correct!* Your score for this answer: ' + answer[1], {parse_mode: 'Markdown'});

} else {

console.log('Nay');

bot.sendMessage(chat_id, 'Your answer was wrong. Your remaining lives: *' + answer[2] +'*', {parse_mode: 'Markdown'})

}

if (answer[2] > 0) {

// new question

console.log('Still lives remaining!')

game.newQuestion(chat_id, gid);

} else {

// game over

console.log('No lives remaining!')

db.gameOver(gid).then((score) => {

console.log('final score: ' + score);

bot.sendMessage(chat_id, '*GAME OVER* :( your final score was *' + score + '*', {

parse_mode: 'Markdown',

reply_markup: {

inline_keyboard: [[

{

text: 'New Game',

callback_data: 'ng_'

}

]]

}

})

}

})

}

else {

bot.sendMessage(data.from.id, 'This question has *already been answered* or has *expired*.', {parse_mode: 'Markdown'})

}

})

}

})

and

//// db.ts

// ...

export function checkAnswerandUpdate(qid, reply) {
    var gid = qid.substring(qid.indexOf('_')+1);

    // Get correct answer
    var p1 = new Promise( (resolve, reject) => {
        let sql = 'SELECT answer_corr FROM questions WHERE id ="' + qid + '"';

        db.all(sql, (err, rows) => {
            if(rows[0].answer_corr==reply) {
                resolve('true');
            } else {                
                resolve('false');
            }
        })
    })

    // Get current streak
    var p2 = new Promise((resolve, reject) => {
        let sql = 'SELECT streak FROM questions WHERE id ="' + qid + '"';

        db.all(sql, function (err, rows) {
            if(rows.length>0) {
                resolve(rows[0].streak)
            } else {
                resolve('no_q')
            }
        })
    })

    // Get lives
    var p3 = new Promise((resolve, reject) => {
        let sql = 'SELECT lives FROM game WHERE id ="' + gid + '"';

        db.all(sql, (err, rows) => {
            resolve(rows[0].lives);
        })
    })

    return Promise.all([p1, p2, p3]).then((results: any) => {
        var score;
        var lives;

        if (results[0] == 'true') {
            score = Math.ceil(results[1]/3) * 50; //Sequential Score Streak increase
            let sql = 'UPDATE questions SET score = ' + score +', user_reply = "' + reply + '", correct = true, active = false WHERE id ="' + qid + '"';
            lives = results[2];
            db.run(sql);
        } else {
            score = 0;
            let streak = (results[1] > 5) ? results[1] - 5 : 1; //Streak penalty
            let sql = 'UPDATE questions SET score = ' + score + ', streak = ' + streak + ', user_reply = "' + reply + '", correct = false, active = false WHERE id ="' + qid + '"';
            db.run(sql);
            //take live!
            lives = results[2]-1;
            sql = 'UPDATE game SET lives = ' + lives + ' WHERE id ="' + gid + '"';
            db.run(sql);
        }
        return ([results[0], score, lives]);
    })
}

//// db.ts

// ...

export function checkAnswerandUpdate(qid, reply) {

var gid = qid.substring(qid.indexOf('_')+1);

// Get correct answer

var p1 = new Promise( (resolve, reject) => {

let sql = 'SELECT answer_corr FROM questions WHERE id ="' + qid + '"';

db.all(sql, (err, rows) => {

if(rows[0].answer_corr==reply) {

resolve('true');

} else {

resolve('false');

}

})

// Get current streak

var p2 = new Promise((resolve, reject) => {

let sql = 'SELECT streak FROM questions WHERE id ="' + qid + '"';

db.all(sql, function (err, rows) {

if(rows.length>0) {

resolve(rows[0].streak)

} else {

resolve('no_q')

}

})

// Get lives

var p3 = new Promise((resolve, reject) => {

let sql = 'SELECT lives FROM game WHERE id ="' + gid + '"';

db.all(sql, (err, rows) => {

resolve(rows[0].lives);

})

return Promise.all([p1, p2, p3]).then((results: any) => {

var score;

var lives;

if (results[0] == 'true') {

score = Math.ceil(results[1]/3) * 50; //Sequential Score Streak increase

let sql = 'UPDATE questions SET score = ' + score +', user_reply = "' + reply + '", correct = true, active = false WHERE id ="' + qid + '"';

lives = results[2];

db.run(sql);

} else {

score = 0;

let streak = (results[1] > 5) ? results[1] - 5 : 1; //Streak penalty

let sql = 'UPDATE questions SET score = ' + score + ', streak = ' + streak + ', user_reply = "' + reply + '", correct = false, active = false WHERE id ="' + qid + '"';

db.run(sql);

//take live!

lives = results[2]-1;

sql = 'UPDATE game SET lives = ' + lives + ' WHERE id ="' + gid + '"';

db.run(sql);

}

return ([results[0], score, lives]);

})

}

And that is pretty much it. There are a couple of more houskeeping and query methods, especially in db.ts which are used for pulling and pushing data from and to SQLite.

So, what now?

In summary we have built a quiz around a Qlik Sense app that allows users to have a game of trivia based on the data and elements available within this app. Without too much effort a couple of additional features could/will be added:

One could pick the 1st entry of the hypercube to ask questions like “which is the top [dimension] by [measure] and then offer the first line value and 2 random other ones to scan for top performing customers/products/months/dates etc. This way there wouldn’t only be numerical (or NaN ;-)) answer options.
One could add field selections or more dimensions to the question/hypercube generation process to create multidimensional problems.
Make the app a variable component. This enables more variety but offers a level of complexity with users and what apps they can access in mind.
A Highscore list (That WILL be done)

I hope you like the idea. Let me know if you do. Let me know if you don’t. Happy hacking!!

Quiz Sense – Who wants to be a Qlikionnaire?

Quiz Sense… uh… BUT WHY??!

OK,I get it – I think – so how does this work?

So, what now?

One Comment on “Quiz Sense – Who wants to be a Qlikionnaire?”

Schreibe einen Kommentar Antworten abbrechen