topic Re: Qliksense logic in to sql script in App Development

Qliksense logic in to sql script

Aspiring_Developer — Tue, 16 Mar 2021 04:44:09 GMT

Hello everyone,

I have below transformed sql in qliksense script :-

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);

Mapping

Load Key as Key1,

tag_TrolleyID_mold

Resident Data;

Mapping

Load Key as Key2,

tag_TrolleyID_paint

Resident Data;

D :

Load Distinct tag_UID

Resident Data;

left join(D)

Load tag_UID,

tag_UID & max(tag_ChangeDate) as key1,

max(tag_ChangeDate) as Datetime_mold

Resident Data where tag_Color = 'NULL' group by tag_UID;

left join(D)

Load tag_UID,

tag_UID & max(tag_ChangeDate) as key2,

max(tag_ChangeDate) as Datetime_paint

Resident Data where tag_Color <> 'NULL' group by tag_UID ;

Final:

Load *,

ApplyMap('A', key1, '-') as tag_TrolleyID_mold,

ApplyMap('B', key2, '-') as tag_TrolleyID_paint

Resident D;

drop tables Data,

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

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

Re: Qliksense logic in to sql script

ArnadoSandoval — Tue, 16 Mar 2021 05:30:09 GMT

Hi @Aspiring_Developer

If I understand you correctly, it seems you have a Qlik Sense script and you want to convert it into a SQL query preserving all the features implemented in QlikSense; If that is your question, perhaps you should ask it in any of the SQL forums; my advice is to create a view in the SQL Server implementing all the QS logic over there, but, if that is what you want, you are in the wrong group.

hth

Re: Qliksense logic in to sql script

Aspiring_Developer — Tue, 16 Mar 2021 14:06:28 GMT

Hi @ArnadoSandoval

I have below sql query.

With Uids AS (
SELECT
distinct t.tag_UID -- Field to be used as a join field on the data model
FROM dbo.Tag_Data AS t
where tag_changer is not null and tag_changer not in ('null') and tag_Changer not like 'Undetected%'
and tag_ProdDate >= '02/18/2021'
),

LastDateMold AS (
SELECT
t.tag_UID -- Field to be used as a join field on the data model
, MAX(t.tag_ChangeDate)as Last_Date
FROM dbo.Tag_Data AS t
where tag_Color = 'NULL'
and tag_changer is not null and tag_changer not in ('null') and tag_Changer not like 'Undetected%'
and tag_ProdDate >= '02/18/2021'
GROUP BY tag_UID
),

LastDatePaint AS (
SELECT
t.tag_UID -- Field to be used as a join field on the data model
, MAX(t.tag_ChangeDate)as Last_Date
FROM dbo.Tag_Data AS t
where tag_Color != 'NULL'
and tag_changer is not null and tag_changer not in ('null') and tag_Changer not like 'Undetected%'
and tag_ProdDate >= '02/18/2021'
GROUP BY tag_UID
)

SELECT
t.tag_UID
,m.tag_UID
,p.tag_UID
,tag1.tag_UID
,tag2.tag_UID
,tag1.tag_TrolleyID
,tag2.tag_TrolleyID
,tag1.tag_ChangeDate
,tag2.tag_ChangeDate
FROM UIDs as t
LEFT JOIN LastDateMold m on t.tag_UID = m.tag_UID
LEFT JOIN LastDatePaint p on t.tag_UID = p.tag_UID
LEFT JOIN dbo.Tag_Data tag1 on m.tag_UID = tag1.tag_UID and m.Last_Date = tag1.tag_ChangeDate
LEFT JOIN dbo.Tag_Data tag2 on p.tag_UID = tag2.tag_UID and p.Last_Date = tag2.tag_ChangeDate
--where t.tag_UID = '555338334d8052220021021812584701'
--where tag1.tag_TrolleyID is not null
-- and tag2.tag_TrolleyID is not null

Can you please tell me how can i run the same script in qliksense ?

Right now i am getting multiple errors.

Please help !!!