Re: Transforming SQL syntax in qliksense script - Qlik Community

Aspiring_Developer · ‎2021-03-09

Hi All,

I have below sql query used for data extraction:-

Data:

LOAD "tag_UID",
"tag_Material",
"tag_Mat_Description",
"tag_SkidID",
"tag_LogicalSkidID",
"tag_SkidPosition",
"tag_Version",
"tag_Color",
"tag_Changer",
"tag_ChangeDate",
"tag_TrolleyID",
"tag_Undetected",
"tag_Mold",
"tag_Cavity",
"tag_ProdDate",
"tag_UndetecCount",
"tag_InternalID",
"tag_IMPCCycleNumber",
"tag_IMPCCompliance",
"tag_IMPCCreatedAt",
"tag_QualityStatus";
SQL SELECT "tag_UID",
"tag_Material",
"tag_Mat_Description",
"tag_SkidID",
"tag_LogicalSkidID",
"tag_SkidPosition",
"tag_Version",
"tag_Color",
"tag_Changer",
"tag_ChangeDate",
"tag_TrolleyID",
"tag_Undetected",
"tag_Mold",
"tag_Cavity",
"tag_ProdDate",
"tag_UndetecCount",
"tag_InternalID",
"tag_IMPCCycleNumber",
"tag_IMPCCompliance",
"tag_IMPCCreatedAt",
"tag_QualityStatus"
FROM PTag.dbo."Tag_Data" where
tag_changer is not null and tag_changer not in ('null') and tag_Changer not like 'Undetected%'
and tag_ProdDate >='01/01/2021';

=====================================

Post extraction, i did some below transformations to optimize the data as per my requirement:-

Data:
LOAD
tag_UID,
tag_Color,
tag_ChangeDate,
tag_UID & tag_ChangeDate as key,
tag_TrolleyID,
// "tag_Material",
// "tag_Mat_Description",
// "tag_SkidID",
// "tag_LogicalSkidID",
// "tag_SkidPosition",
if(tag_Color = 'NULL', tag_TrolleyID ) as tag_TrolleyID_mold,
if(tag_Color <> 'NULL', tag_TrolleyID ) as tag_TrolleyID_paint

FROM [......]
(qvd)

where tag_TrolleyID <>'NULL' and tag_TrolleyID <>'';
//tag_ProdDate >='2/18/2021'and
//Exit Script;

A:
Mapping
Load key as Key1, tag_TrolleyID_mold Resident Data;

B:
Mapping
Load key as Key2, tag_TrolleyID_paint Resident Data;

D :
load distinct tag_UID resident Data;

left join(D)

Load
tag_UID,
//tag_Color,
tag_UID & max(tag_ChangeDate) as key1,
max(tag_ChangeDate) as Datetime_mold
Resident Data where tag_Color = 'NULL' group by tag_UID; //tag_Color;

left join(D)

Load
tag_UID,
//tag_Color ,
tag_UID & max(tag_ChangeDate) as key2,
max(tag_ChangeDate) as Datetime_paint
Resident Data where tag_Color <> 'NULL' group by tag_UID ;//tag_Color;

Final:
Load *,
ApplyMap('A', key1, '-') as tag_TrolleyID_mold,
ApplyMap('B', key2, '-') as tag_TrolleyID_paint
Resident D;

drop tables Data, D;

Store Final into ....

=======================================================================

Now i want to do the above transformatiion in the SQL extractor but i am not very much aware of the sql syntaxes.

Can anyone please guide me on how can i do the qliksesne script transformation directly in the SQL query extractor.

The purpose of doing this is to reduce the load time and get desired number of rows.

Can any please help me .... ?

berkarmagan · ‎2021-03-10

Hi,

First of all, getting data directly from SQL will not solve ur problem I think because it is another process that you should wait for it. If your sql server is fast enough maybe it would decrease your load time but i think you should think about getting data from the qvds without optimized load it can be a problem. So i think try to get data from qvd with optimized process which you shouldn't use where clause s in your script. It would increase your data load speed..

By the way if you need to do it on sql server anyway you should just join tables in sql which you're doing in qlik script like ;

with data as (Select * FROM PTag.dbo."Tag_Data" where
tag_changer is not null and tag_changer not in ('null') and tag_Changer not like 'Undetected%'
and tag_ProdDate >='01/01/2021')

select * from data x

left join (tag_UID,
convert(nvarhcar(10),tag_UID) +''+ convert(nvarhcar(10),max(tag_ChangeDate)) as key1,
max(tag_ChangeDate) as Datetime_mold
from Data where tag_Color = 'NULL' group by tag_UID)y on y.tag_UID=x.tag_UID

Aspiring_Developer · ‎2021-03-10

Hi @berkarmagan

Thank you for your response.

My requirement is to tranform the select query syntax in qliksense script itslef .

Data:

LOAD "tag_UID",
"tag_Material",
"tag_Mat_Description",
"tag_SkidID",
"tag_LogicalSkidID",
"tag_SkidPosition",
"tag_Version",
"tag_Color",
"tag_Changer",
"tag_ChangeDate",
"tag_TrolleyID",
"tag_Undetected",
"tag_Mold",
"tag_Cavity",
"tag_ProdDate",
"tag_UndetecCount",
"tag_InternalID",
"tag_IMPCCycleNumber",
"tag_IMPCCompliance",
"tag_IMPCCreatedAt",
"tag_QualityStatus";
SQL SELECT "tag_UID",
"tag_Material",
"tag_Mat_Description",
"tag_SkidID",
"tag_LogicalSkidID",
"tag_SkidPosition",
"tag_Version",
"tag_Color",
"tag_Changer",
"tag_ChangeDate",
"tag_TrolleyID",
"tag_Undetected",
"tag_Mold",
"tag_Cavity",
"tag_ProdDate",
"tag_UndetecCount",
"tag_InternalID",
"tag_IMPCCycleNumber",
"tag_IMPCCompliance",
"tag_IMPCCreatedAt",
"tag_QualityStatus"
FROM PTag.dbo."Tag_Data" where
tag_changer is not null and tag_changer not in ('null') and tag_Changer not like 'Undetected%'
and tag_ProdDate >='01/01/2021';

I wish to do the transformation here in the qliksense select query (no in the sql server)

Thanks

berkarmagan · ‎2021-03-10

Ok but if you need the change sql statemnts in qlik scripts - it means it will also run on SQL server, if you write your code even in sql management studio or Qlik script.

I hope i dont understand ur problem wrong but anyway if you want do all the things in Qliksense script editor with sql statements, you can use a query like that my previous post. But i don't reccomend that if i m clear about the problem.

Aspiring_Developer · ‎2021-03-15

Hello @berkarmagan

I did some changes and transformed the sql in qliksense script like below:-

Data:
LOAD *;

SQL SELECT

"tag_UID",

"tag_Material",

"tag_Mat_Description",

"tag_SkidID",

"tag_LogicalSkidID",

"tag_SkidPosition",

"tag_Version",

"tag_Color",

"tag_Changer",

"tag_ChangeDate",

"tag_TrolleyID",

"tag_Undetected",

"tag_Mold",

"tag_Cavity",

"tag_ProdDate",

"tag_UndetecCount",

"tag_InternalID"

FROM ( SELECT tag_UID a, MAX(tag_ChangeDate) AS Max_Datetime

FROM PTag.dbo."Tag_Data"

where tag_changer is not null and tag_changer not in ('null')

GROUP BY tag_UID) AS x INNER JOIN

PTag.dbo."Tag_Data" ON

x.a = tag_UID and x.Max_Datetime=tag_ChangeDate and tag_ProdDate >= '01/01/2021' ;

=================================================================================

Now, how should i achieve the below qliksense tranforamtion in the above script:-

Data:
LOAD
tag_UID,
tag_Material,
tag_Mat_Description,
tag_SkidID,
tag_LogicalSkidID,
tag_SkidPosition,
tag_Version,
tag_Color,
IF( "tag_Color" = 'NULL', "tag_TrolleyID" ) as tag_TrolleyID_mold,
IF( "tag_Color" <> 'NULL', "tag_TrolleyID" ) as tag_TrolleyID_paint,
"tag_UID" & "tag_ChangeDate" as Key,
tag_Changer,
tag_ChangeDate,
tag_TrolleyID,
tag_Undetected,
tag_Mold,
tag_Cavity,
tag_ProdDate,
tag_UndetecCount,
tag_InternalID
FROM [.......qvd]
(qvd);

A:
Mapping
Load Key as Key1, tag_TrolleyID_mold Resident Data;

B:
Mapping
Load Key as Key2, tag_TrolleyID_paint Resident Data;

D :
Load Distinct tag_UID Resident Data;

left join(D)

Load
tag_UID,
//tag_Color,
tag_UID & max(tag_ChangeDate) as key1,
max(tag_ChangeDate) as Datetime_mold
Resident Data where tag_Color = 'NULL' group by tag_UID; //tag_Color;

left join(D)

Load
tag_UID,
//tag_Color ,
tag_UID & max(tag_ChangeDate) as key2,
max(tag_ChangeDate) as Datetime_paint
Resident Data where tag_Color <> 'NULL' group by tag_UID ;//tag_Color;

Final:
Load *,
ApplyMap('A', key1, '-') as tag_TrolleyID_mold,
ApplyMap('B', key2, '-') as tag_TrolleyID_paint
Resident D;

drop tables Data, D;

================================

I need to do the qliksense script logic in the sql query in order to combine the result in one extractor. Insted of first extracting the data into qvd and then transformin it.

I tried the syntax that you gave earlier but i am unable to achieve it.

Can you please help as i am stuck on it from a long time now ? 😞 Please help

@Kushal_Chawda @sunny_talwar

Aspiring_Developer · ‎2021-03-15

Hi , can anyone please help on this ? Please !

Aspiring_Developer · ‎2021-03-16

Hi @berkarmagan

I tried your query but it is giving me syntax error:-

Data:
LOAD *,

IF( "tag_Color" = 'NULL', "tag_TrolleyID" ) as tag_TrolleyID_mold,
IF( "tag_Color" <> 'NULL', "tag_TrolleyID" ) as tag_TrolleyID_paint,
;

SQL (SELECT

*

FROM PTag.dbo."Tag_Data"

where tag_changer is not null and tag_changer not in ('null') and tag_changer not like 'Undetected%' and

tag_UID='555338334d8492330021021805073101' and tag_TrolleyID <>''and tag_ProdDate >='01/01/2021'

) AS x LEFT JOIN on

PTag.dbo."Tag_Data"

(tag_UID,
convert(nvarhcar(10),tag_UID) +''+ convert(nvarhcar(10),max(tag_ChangeDate)) as key1,
max(tag_ChangeDate) as Datetime_mold
from Data where tag_Color = 'NULL' group by tag_UID)y on y.tag_UID=x.tag_UID
;

Could you please tell me what i did wrong ?