topic Load Data using SQL in QlikView

Load Data using SQL

jonathanEVS — Tue, 28 May 2024 14:44:02 GMT

Hello everyone, i'm pretty new to QLIK and I need some help because i'm desesperate.
I created a table manually (i had a csv, and find a way to recreate it in SQL to manipulate) and use it to manipulate datas using other tables from an Oracle data base.I test my script using DBeaver. Here is a part of my code :

WITH cte_correspondance_grammage AS (
SELECT 'MILANO' AS MACHINE,
'Ligne de prix' AS TYPE_PRODUIT,
'BOISSONS CHAUDES' AS GROUPE,
'CAFE NOIR' AS TYPE,
'CAFE LYOPHILISE' AS FAMILLE,
'NESTUSA' AS CODE,
'NESTLE AMERICANO' AS NOM,
2.1 AS Cafe_grain,
0.0 AS Cafe_Lyo,
0.0 AS Chocolat,
0 AS Lait,
190 AS Eau_ml
FROM dual
UNION ALL
SELECT 'MILANO', 'Ligne de prix', 'BOISSONS CHAUDES', 'BOISSON GOURMANDE', 'CHOCOLAT', 'NESTCACAO', 'NESTLE CACAO',
0.0, 0.0, 24.8, 0, 150
FROM dual
UNION ALL
SELECT 'MILANO', 'Ligne de prix', 'BOISSONS CHAUDES', 'BOISSON GOURMANDE', 'CHOCOLAT', 'NESTVIENNOIS', 'NESTLE CACAO VIENNOIS',
0.0, 0.0, 18.6, 6.3, 150
FROM dual
UNION ALL
SELECT 'MILANO', 'Ligne de prix', 'BOISSONS CHAUDES', 'BOISSON GOURMANDE', 'CAFE LYOPHILISE', 'NESTCAFELAIT', 'NESTLE CAFE AU LAIT',
2.6, 0.0, 0.0, 6, 150
FROM dual
),
PRODUCT_INFOS AS (
SELECT
p.IDPRODUCT,
p.CODE,
p.NAME,
l.CODE AS FAMILLE_p
FROM
PRODUCT p
JOIN
LABEL_HAS_PRODUCT lhp ON p.IDPRODUCT = lhp.IDPRODUCT
JOIN
LABEL l ON lhp.IDLABEL = l.IDLABEL
WHERE l.CODE IN ('LAIT','CHOCOLAT','CAFE GRAIN','CAFE LYOPHILISE') AND p.NAME NOT IN ('NESTLE CAFE 250G','MONBANA CHOCOLAT MAX HAVELAAR 15G','VAN HOUTEN CHOCOLAT 23G','PATURAGES LAIT BIO DEMI ECREME 1L')
),
PRODUCT_AND_MP AS(
SELECT
p.*,
cg.cafe_lyo,
cg.cafe_grain,
cg.chocolat,
cg.lait,
cg.Eau_ml,
CASE
WHEN cg.cafe_lyo IS NULL AND cg.cafe_grain IS NULL AND cg.chocolat IS NULL AND cg.lait IS NULL AND cg.Eau_ml IS NULL THEN 1
ELSE 0
END AS premiere
FROM PRODUCT_INFOS p
LEFT JOIN cte_correspondance_grammage cg ON cg.NOM=P.NAME)
... (i then do other manipulation) etc
 
But I saw that in Qlik I can't use the "dual" option to load data table. Is there an alternative to achieve t
in QLIK ?
i'd be very grateful if you can help me

PS : My table i create manually is from an csv file and it got 28 lines (i didnt write it all in this post) if it can help

Re: Load Data using SQL

marksouzacosta — Tue, 28 May 2024 19:55:04 GMT

Before digging into this, can you just create a View in your Database adding all this code there?
Doing that, your Qlik code will be simple as SQL SELECT * FROM MyVew;.
Does that work for you?

Re: Load Data using SQL

jonathanEVS — Wed, 29 May 2024 11:48:00 GMT

The thing is that in my oracle data base i'm not admin so I can't create a table (if it's what you mean).

But I'm building my own data base on postreSQL by fetching data from the oracle data base, because I don't see any other solutions

Re: Load Data using SQL

marksouzacosta — Wed, 29 May 2024 13:24:16 GMT

I have the feeling your problem is not with the DUAL.
In any case, you can definitely rewrite your queries to work in Qlik Load Script.
Load the tables isolated and combine them with Qlik. This will look like this - I was not able to test:

PRODUCT_INFOS: SQL SELECT p.Name AS %ProductLink, p.IDPRODUCT AS ProductID, p.CODE AS ProductCode, p.NAME AS ProductName, l.CODE AS FAMILLE_p FROM PRODUCT p JOIN LABEL_HAS_PRODUCT lhp ON p.IDPRODUCT = lhp.IDPRODUCT JOIN LABEL l ON lhp.IDLABEL = l.IDLABEL WHERE l.CODE IN ('LAIT','CHOCOLAT','CAFE GRAIN','CAFE LYOPHILISE') AND p.NAME NOT IN ('NESTLE CAFE 250G','MONBANA CHOCOLAT MAX HAVELAAR 15G','VAN HOUTEN CHOCOLAT 23G','PATURAGES LAIT BIO DEMI ECREME 1L') ; LEFT JOIN (PRODUCT_INFOS) cte_correspondance_grammage: LOAD NOM AS %ProductLink, MACHINE, TYPE_PRODUIT, GROUPE, TYPE, FAMILLE, CODE, NOM, Cafe_grain, Cafe_Lyo, Chocolat, Lait, Eau_ml INLINE [ MACHINE, TYPE_PRODUIT, GROUPE, TYPE, FAMILLE, CODE, NOM, Cafe_grain, Cafe_Lyo, Chocolat, Lait, Eau_ml MILANO, Ligne de prix, BOISSONS CHAUDES, CAFE NOIR, CAFE LYOPHILISE, NESTUSA, NESTLE AMERICANO, 2.1, 0.0, 0.0, 0, 190 MILANO, Ligne de prix, BOISSONS CHAUDES, BOISSON GOURMANDE, CHOCOLAT, NESTCACAO, NESTLE CACAO, 0.0, 0.0, 24.8, 0, 150 MILANO, Ligne de prix, BOISSONS CHAUDES, BOISSON GOURMANDE, CHOCOLAT, NESTVIENNOIS, NESTLE CACAO VIENNOIS, 0.0, 0.0, 18.6, 6.3, 150 MILANO, Ligne de prix, BOISSONS CHAUDES, BOISSON GOURMANDE, CAFE LYOPHILISE, NESTCAFELAIT, NESTLE CAFE AU LAIT, 2.6, 0.0, 0.0, 6, 150 ]; PRODUCT_AND_MP: LOAD // Fields from PRODUCT_INFOS %ProductLink, ProductID, ProductCode, ProductName, FAMILLE_p, // Fields from cte_correspondance_grammage cafe_lyo, cafe_grain, chocolat, lait, Eau_ml, If(IsNull(cafe_lyo) AND IsNull(cafe_grain) And IsNull(chocolat) AND IsNull(lait) AND IsNull(Eau_ml),1,0) AS premiere RESIDENT PRODUCT_INFOS ; DROP TABLE PRODUCT_INFOS;

Re: Load Data using SQL

jonathanEVS — Thu, 30 May 2024 08:09:30 GMT

Thank you for your response Sir. I'm still not really familiar with Qlik script so this help me a lot!