ChatGPT in Qlik Sense

[jptneumann]

ChatGPT , developed by OpenAI, is the subject of the moment and is already the 4th most used "search engine" in the world, surpassing Bing and DuckDuckGo according to data from the traffic analysis site Similar Web (2023Abr04 - https:// www .similarweb.com/blog/insights/ai-news/chatgpt-bing-duckduckgo ). ChatGPT is a deep learning chatbot that comes with a set of advanced artificial intelligence features to help users solve complex problems. It uses natural language processing techniques to understand users conversations and provide intelligent responses. It can also learn and adjust responses based on user experience. ChatGPT is a great tool for anyone who wants to build custom chat apps.

I was recently asked if Qlik Sense can integrate with ChatGPT. And the answer is YES !

Access to ChatGPT can be done through an API provided by OpenAI. To do so, you need to have an OpenAI account, generate an access token and register a credit card. The use of the ChatGPT API is paid, varying according to the model used, each with different capacities and prices. Prices are per 1000 tokens. You can think of tokens as chunks of words, where 1000 tokens is about 750 words. There is how to configure usage limits. So be careful when using it so you don't have a surprise on your credit card statement.

Using ChatGPT in Qlik Sense SaaS

Let's show a simple example of integrating Qlik Sense in a SaaS environment with ChatGPT , where we built a screen to ask questions to ChatGPT. Initially you need an OpenIA API access token.

Let's create a screen with the following elements:

• A text input, to write the desired search;
• A Text Box To display the ChatGPT response;
• A button to reload the application whenever you do a new search

The screen looked like this:

Next, let's create a load script, with a REST connector, to make requests to ChatGPT.

- Initially declare the variables needed to perform the search:

[TextChatGPT]:

LIB CONNECT TO 'OpenAI';

Let vOpenApiToken='<SEU TOKEN>';
Let vOpenAIUrlApi='https://api.openai.com/v1/completions';

Let vBody='{
 "model": "text-davinci-003",
 "prompt": "$(vPergunta)",
 "max_tokens": 250,
 "temperature": 0.7
}';

Let vBody = replace(vBody,'"', chr(34)&chr(34));

 
We've created a REST connector called OpenAI. Let's use the text-davinci-003 template (I put ChatGPT3 here since currently few have access to the ChatGPT4 beta). The vOpenApiToken variable should contain your token. The vQuestion variable will be associated with the screen's text Input object.

Then we will make the API request:

RestMasterTable:
SQL SELECT 
	"id",
	"object",
	"created",
	"model",
	"__KEY_root",
	(SELECT 
		"text",
		"index",
		"logprobs",
		"finish_reason",
		"__FK_choices"
	FROM "choices" FK "__FK_choices"),
	(SELECT 
		"prompt_tokens",
		"completion_tokens",
		"total_tokens",
		"__FK_usage"
	FROM "usage" FK "__FK_usage")
FROM JSON (wrap on) "root" PK "__KEY_root"
WITH CONNECTION(URL "$(vOpenAIUrlApi)", 
		BODY "$(vBody)",
 		HTTPHEADER "Authorization" "Bearer $(vOpenApiToken)",
 		HTTPHEADER "Content-Type" "application/json");

 So just collect the data in a table, and delete the RestMasterTable:

[choices]:
LOAD	
	[text],
	[index],
	[logprobs],
	[finish_reason]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_choices]);

DROP TABLE RestMasterTable;

And ready. Just load and the answer to your search in ChatGPT will be in the text field.

Going to our screen, just fill in the question text and press the button, wait for the reload and that's it. Your answer will be on the screen!

As an example, I'll ask ChatGPT to " List 5 typical dishes from Alagoas " (for those who know the State of Alagoas in the Northeast of Brazil, you know that we have a rich cuisine).

And the answer was:

1. Carne de sol com macaxeira
2. Moqueca de peixe;
3. Arroz de caranguejo;
4. Baião-de-dois;
5. Cuscuz de tapioca.;

And so the ChatGPT oracle is built into Qlik Sense.

Soon I will publish a second part generating images dynamically in Qlik Sense via ChatGPT.

🖖🏻 See you next time!!!

PS: This article was originally published in Portuguese on linkedin. see in https://www.linkedin.com/pulse/chatgpt-qlik-sense-jean-paul-neumann/

 #QlikPartnerAmbassador

#NTechTI

#AnalyticsLab

#Qlik

[sumitk]

I am not able to see vQuestion variable in your article how you created and used them

[jptneumann]

Hi @sumitk !

You can create the vQuestion variable at the beginning of the script:

Let vQuestion = '';

Or using the variable editor, available on the editing screen:

And associate it to your question input field.

[happyfeet11]

Hi 

 may be anyone can give me an headsup about the expections about the cost for a 30min usage 

best Indrani

[SergiiEC]

Big thanks to author, topic was very helpful!

For future references - in vBody variable we need to use $(vQuestion) instead of $(vPergunta)

[jptneumann]

Hi @happyfeet11 ,
Prices are not per time, are per 1,000 tokens. You can think of tokens as pieces of words, where 1,000 tokens is about 750 words. More information about prices, see: https://openai.com/pricing

[jptneumann]

Hi @SergiiEC ,
Yes you are correct. This post was originally written in Portuguese on Linkedin, and then translated into English. There was a translation fail (Question[en]=Pergunta[pt]). Thank's for the comment and I hope this article was useful to you.

[Kathlyn]

How does ChatGPT contribute to data exploration and analysis workflows within Qlik Sense?

[Narges]

@jptneumann is this possible to use chat gpt by API calling in QS enterprise version? not SaaS.

[jptneumann]

Hi @Kathlyn ,

Generative AI (GenAI) is a powerful tool for improving efficiency, innovation and customer experience in your business  Consider exploring how this technology can be applied specifically to your industry and needs.

How to explore GenAI? See some examples:

Custom Content Creation:
Generate personalized content for your customers, such as product recommendations, marketing messages, and answers to frequently asked questions.
This improves the customer experience and increases engagement

Design and Innovation:
GenAI can help with product design, creating variations and prototypes. It can also generate new ideas and concepts for continuous innovation.

Process Optimization:
GenAI can automate repetitive tasks such as reporting, data analysis and supply chain optimization, saves time and resources, allowing you to focus on strategic activities.

Forecasts and Analysis:
GenAI can predict market trends, customer demand and consumer behavior, helping you make informed decisions and identify opportunities.

In short, Generative AI is transforming every industry, delivering significant improvements in customer service, product innovation and process optimization. It is a powerful tool for boosting company profitability and efficiency.

I hope I answered your question as expected.

[jptneumann]

Hello @Narges,

Sure. All you need is to use the REST connector available in the Qlik Sense enterprise version On-premises.