Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

achakilam1022
Contributor

SQL statement in Load (QlikSense)

Hi all,

I've the following table

boberdoo:

LOAD id,

    transaction_date,

    `b_lead_id_unique`,

    email,

    phone;

SQL SELECT *

FROM forms.`boberdoo_leads_master`

;

Question: I need help to embed the following SQL Query into the Load statement. This query modifies the original table and adds column flag to the db table. Instead I want to add this column only to the boberdoo table in Qlik and not modify the table in database.


select a.id, a.transaction_date, a.phone, a.email,

case when a.phone is null and a.email is null then null

when sum(case when b.phone is not null or c.email is not null then 1 else 0 end) > 0 then 'Duplicate' else 'Unique' end as flag

from forms.`boberdoo_leads_master` a

left join forms.`boberdoo_leads_master` b on (a.phone = b.phone and a.transaction_date > b.transaction_date)

left join forms.`boberdoo_leads_master` c on (a.email = c.email and a.transaction_date > c.transaction_date)

group by a.id, a.transaction_date, a.phone, a.email

Thanks.

1 Solution

Accepted Solutions
mov
Esteemed Contributor III

Re: SQL statement in Load (QlikSense)

"The original data table doesn't contain the column flag"-Correct.

" If I use this SQL query it generates a flag column in the table in database." - Wrong.    This SQL reads tada from the database without making any changes in the database tables.

It creates field in Qlik while loading the data.

I hope this helps to understand my answer better.

8 Replies
mov
Esteemed Contributor III

Re: SQL statement in Load (QlikSense)

If your query works on SQL, you can use it as is in the Qlik script.  It's not going to modify the database table.

rohitk1609
Valued Contributor II

Re: SQL statement in Load (QlikSense)

Hi Amuakha,

The query you have written is a simple SQL query

select a.id, a.transaction_date, a.phone, a.email,

case when a.phone is null and a.email is null then null

when sum(case when b.phone is not null or c.email is not null then 1 else 0 end) > 0 then 'Duplicate' else 'Unique' end as flag

from forms.`boberdoo_leads_master` a

left join forms.`boberdoo_leads_master` b on (a.phone = b.phone and a.transaction_date > b.transaction_date)

left join forms.`boberdoo_leads_master` c on (a.email = c.email and a.transaction_date > c.transaction_date)

group by a.id, a.transaction_date, a.phone, a.email

So in Qlik , You first load the table from data source, by creating an ODBC connection and automatic script will be generated, now the condition you have written for outcome as

left join forms.`boberdoo_leads_master` b on (a.phone = b.phone and a.transaction_date > b.transaction_date)

left join forms.`boberdoo_leads_master` c on (a.email = c.email and a.transaction_date > c.transaction_date)

group by a.id, a.transaction_date, a.phone, a.email

It is kind of self join and filtering data on multiple conditions and having group by clause. So you may write it is

Load

*,

'1' as Flag

from

boberdoo_leads_master


WHERE


Conditions of filtering


GROUP BY

transaction_date,

phone,

email

;


///////////

case when a.phone is null and a.email is null then null

when sum(case when b.phone is not null or c.email is not null then 1 else 0 end) > 0 then 'Duplicate' else

For this , you can use IF statement in Qlik, you may read on community or in help how to use it

///////////////


In qlik by load and select statement you can only fetch the data for modify the data in SQL server, you may use

SQL write update or delete command or any instruction of SQL


Please load the table first in Qlik and try to do what I have stated and let me know where you stcuk.


Please mark the appropriate replies as CORRECT / HELPFUL so our team and other members know that your question(s) has been answered to your satisfaction.

Rohit Kumar


achakilam1022
Contributor

Re: SQL statement in Load (QlikSense)

Hi Rohit,

I'm unable to use group by as there are no aggregations


Thanks

rohitk1609
Valued Contributor II

Re: SQL statement in Load (QlikSense)

As there are no aggregation means, there is no requirement of group by or it is giving you some syntax error ?

Re: SQL statement in Load (QlikSense)

Hi ,

You can use SQL statement in Qlikview itself. So just try like below

boberdoo:

LOAD id,

    transaction_date,

    `b_lead_id_unique`,

    email,

    phone;

SQL select a.id, a.transaction_date, a.phone, a.email,

case when a.phone is null and a.email is null then null

when sum(case when b.phone is not null or c.email is not null then 1 else 0 end) > 0 then 'Duplicate' else 'Unique' end as flag

from forms.`boberdoo_leads_master` a

left join forms.`boberdoo_leads_master` b on (a.phone = b.phone and a.transaction_date > b.transaction_date)

left join forms.`boberdoo_leads_master` c on (a.email = c.email and a.transaction_date > c.transaction_date)

group by a.id, a.transaction_date, a.phone, a.email;

achakilam1022
Contributor

Re: SQL statement in Load (QlikSense)

Hi,

The original data table doesn't contain the column flag. If I use this SQL query it generates a flag column in the table in database. Instead, I want to generate this field only in Qlik while loading the data. I hope this helps you understand my question better.

Thanks

mov
Esteemed Contributor III

Re: SQL statement in Load (QlikSense)

"The original data table doesn't contain the column flag"-Correct.

" If I use this SQL query it generates a flag column in the table in database." - Wrong.    This SQL reads tada from the database without making any changes in the database tables.

It creates field in Qlik while loading the data.

I hope this helps to understand my answer better.

achakilam1022
Contributor

Re: SQL statement in Load (QlikSense)

Thanks a lot for the clear explanation Michael

Community Browser