topic Re: Fetching data from SQL giving more than it should in App Development

Fetching data from SQL giving more than it should

sajalgour2309 — Sat, 16 Nov 2024 21:48:52 GMT

Hello All,

I have a strange case and I am unable to figure out what is going wrong.

I have one SQL query which I run in Teradata client and it returns me 516851 rows and when I run same SQL query in Qlik Sense Ii fetches 517543.

below is the query which run -

select Ss_Dt_Uid,Mnth_Uid,KPI_Group3 as KPI_Group2,Scorecard_Dealer_id as Dealer_Cd,Sum(Measure1) as Measure ,Sum(Total_Promotors) as Total_Promotors,Sum(Total_Detractors) as Total_Detractors
,Sum(Closing_Base_Me) as Closing_Base_Me ,Sum(Closing_Base_Daily) as Closing_Base_Daily ,Sum(Smart_Device_Sales) as Smart_Device_Sales,Sum(VAS_Addressable) as VAS_Addressable ,Sum(Total_Neutrals) as Total_Neutrals, j4u_band from mi_datalab.sg_qlik_pigex3
group by Ss_Dt_Uid,Mnth_Uid,KPI_Group3,j4u_band,Scorecard_dealer_id
where dealer_cd not in
( select distinct B.Dealer_cd from mi_datalab.nv_channel_info A
inner join
(sel distinct dealer_cd from mi_datalab.sg_qlik_pigex3 ) B
on A.Dealer_cd = B.Dealer_cd
where A.Sub_Channel like '%pop%' or A.Sub_Channel like '%canv%')
and mnth_uid > '201803' and Ss_Dt_Uid <= (select max(Ss_Dt_Uid)-3 from mi_datalab.sg_qlik_pigex3)

I am not loading any other table in qlik just this SQL so no chance of concatenate or join or something...

I don't know what I am doing wrong here ? Attaching some snapshots...

SQL load in TeradataSQL load in Qlik Sense

Re: Fetching data from SQL giving more than it should

Gysbert_Wassenaar — Wed, 28 Nov 2018 17:57:15 GMT

Are you sure that you're querying exactly the same database? And that in both cases you're querying the same data set? There are no records that you can't see in your session because of transactions that were commited after you started your session? No concurrency issues etc?

Re: Fetching data from SQL giving more than it should

CathyRDuvall — Wed, 28 Nov 2018 17:57:45 GMT

Looks like your are running a Preceding Load statement in QS which is not needed if you are not making any transformations of the data coming from SQL Select statement.

Try removing: Load * ;

Re: Fetching data from SQL giving more than it should

sajalgour2309 — Fri, 30 Nov 2018 09:28:03 GMT

I have tried removing load* but still same. But if I remove the date parameter (last line of SQL where I say mnth_uid >201803 ) it works...

only removing this part(and mnth_uid > '201803' and Ss_Dt_Uid <= (select max(Ss_Dt_Uid)-2 from mi_datalab.sg_qlik_pigex3))

I have checked again and again it is same database same query and no other table.

Re: Fetching data from SQL giving more than it should

CathyRDuvall — Fri, 30 Nov 2018 14:31:40 GMT

Please check out code attached for QS below. There are comments that explain what you need to change between coding in SQL and QS. There was no way that I could test this on my system so send me the error message if you get one.

Re: Fetching data from SQL giving more than it should

sajalgour2309 — Mon, 03 Dec 2018 13:17:50 GMT

Hello,

I have tried your script. Exact script was not working but I have got the idea what you want or trying to do here. I have changed my script as per your suggestion and it works.

But I am still confused and don't why this is happening ? I used to think that Qlik doesn't do anything just pass your SQL script to your database but I don't think it is happening here...?

Regards,

Sajal

Re: Fetching data from SQL giving more than it should

CathyRDuvall — Mon, 03 Dec 2018 13:25:41 GMT

The coding is different in QS then it is for SQL. Please take a moment to explore this link:

https://help.qlik.com/en-US/sense/November2018/Content/Sense_Helpsites/Tutorials/Tutorials-scripting-beginners.htm

Good luck and don't hesitate to ask future questions.