SQL statement in Load (QlikSense)

[achakilam1022]

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.

[Anonymous]

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]

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]

Hi Rohit,

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

Thanks

[rohitk1609]

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;

[achakilam1022]

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 · Accepted solution]

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

Thanks a lot for the clear explanation Michael

[MounikaS]

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.

Am i doing any mistake here -