Skip to main content
Announcements
Accelerate Your Success: Fuel your data and AI journey with the right services, delivered by our experts. Learn More
cancel
Showing results for 
Search instead for 
Did you mean: 
achakilam1022
Creator II
Creator II

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
Anonymous
Not applicable

"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.

View solution in original post

9 Replies
Anonymous
Not applicable

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
Master
Master

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
Creator II
Creator II
Author

Hi Rohit,

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


Thanks

rohitk1609
Master
Master

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

MayilVahanan

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;

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.
achakilam1022
Creator II
Creator II
Author

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

Anonymous
Not applicable

"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
Creator II
Creator II
Author

Thanks a lot for the clear explanation Michael

MounikaS
Contributor
Contributor

Dear all,

Could you please let me know why am i getting below error. I have worked with Views earlier , it worked fine. I am trying to call procedure , now its giving an error. Please help me out.

MounikaS_0-1589963425393.png

 

Am i doing any mistake here -

MounikaS_1-1589963508740.png