Counts mismatch in both teradata and Qlik, due to nulls or ? I think. how to match with Teradata count
below is the teradata script and when I try to convert it to Qlik, instead of 6 records for 202405 i am getting only 1 record in the Qlik.
SELECT B.Subscr_Id, tld.disc_start_date, tld.disc_end_date, Life.Start_Date, Life.End_date FROM Base B LEFT JOIN discount_hist tld ON B.subscr_id = tld.subscr_id AND B.calendar_date BETWEEN tld.disc_start_date AND tld.disc_end_date LEFT JOIN Subscr_Life_Cylce Life ON B.subscr_id = Life.subscr_id AND B.calendar_date BETWEEN Life.Start_Date AND Life.End_date.
now after executing this query for calendar month 202405 we are getting records of 6 and out of that only 1 subscr_id contains the all dates like tld.disc_start_date, tld.disc_end_date, Life.Start_Date, Life.End_date to compare it with the calendar_date. remaining 5 subscr_ids do not have tld.disc_start_date, tld.disc_end_dates in the data. they are just displaying as ? ...but when I implement the same query in the Qlik I am getting only 1 single record as my condition in the Qlik script looks like below I took each table as did a left join to the base and to the final Base i have taken a resident load and applied this where condition as followed - where (calendar_Date >= disc_start_date AND Calendar_Date <= Disc_End_Date) AND (calendar_Date >= start_date AND Calendar_Date <= End_Date) how should i match with the SQL query result.
In this case, I think Date() is the function to use, but the origin format can make it more complicated, maybe you need Date(Date#()) or Date(Num#()) if the values are readed as text.
You can also try to a field like "RowNo() as "RowNumber", to confirm if there are different rows, but with the same values.
If you keep getting one row try to do the joins step by step trying to capture what is failing in each join.
here is the result of 6 rows from teradata query. where as in qlik we are getting only one record which is subscr_id 101 - as it satisfying the where conditions that i have written in the final Base table
The ? is normally a place-holder if no (valid) data-interpretation exists respectively could be applied. To look for the stored content you may apply a precending-load, for example with stuff like:
load *, date(date#(YourField, 'DD.MM.YYYY')) as x, text(YourField) as y, isnum(YourField) as z, len(YourField) as l; select ... from ...;
It should give valuable hints which kind of data is really there.
these ? are from the source tables it self. and when I check in the chat GPT for the counts mismatch it is suggested to use the below condition to match with teradata results.
Teradata join condition is like below.
select * FROM Base B LEFT JOIN discount_hist tld ON B.subscr_id = tld.subscr_id AND B.calendar_date BETWEEN tld.disc_start_date AND tld.disc_end_date LEFT JOIN Subscr_Life_Cylce Life ON B.subscr_id = Life.subscr_id AND B.calendar_date BETWEEN Life.Start_Date AND Life.End_date LEFT JOIN Device D ON B.subscr_id = D.subscr_id AND B.calendar_date BETWEEN D.Start_Date AND D.End_date
Chat GPT suggestion is to use the if condition and derive flags as below and use these flags as '1' by taking the resident of final Base table.
if((NOT isnull(disc_start_Date) AND Calendar_Date >= Disc_Start_Date and Calendar_Date <= Disc_end_Date) OR isnull(Disc_End_Date),1,0) as Discount_Match,
if((NOT isnull(Lifecycle_Start_Date) AND Calendar_Date >= Lifecycle_Start_Date and Calendar_Date <= Lifecycle_End_Date) OR isnull(Lifecycle_End_Date),1,0) as Life_Match,
if((NOT isnull(start_Date) AND Calendar_Date >= Start_Date and Calendar_Date <= End_Date) OR isnull(End_Date),1,0) as Device_Match;
Final:
Load *
Resident Base
Where Discount_Match = 1 AND Life_Match = 1 AND Device_Match = 1;
After this my qlik report counts are reduced and not matching with the sql. in general sql query looks simple but I am not able to replicate it into QLIk, kindly help here, how can I implement the exact sql in the Qlik. many thanks in advance.
Ok, if ? comes from the source, then the where coindition will exclude them, if you want to include in the query you have 2 options:
- Convert in a previous step the ? to dates, in example start could be Date(MakeDate(2024),'DD.MM.YYYY') and end could be Date(MakeDate(9999,12,31),'DD.MM.YYYY')
- Add an 'or' to the where, like: ((calendar_Date >= disc_start_date or disc_start_date = '?') AND (Calendar_Date <= Disc_End_Date or Disc_End_Date = '?'))
Also, the ? can explain why it doesn't get any rows fron the discount_hist, but the Subscr_Life_Cylce should work, maybe the values are string instead of dates.
You can just load the tables separatedly and add the fields to a table in design, if they show left-aligned it will mean that is being loaded as a text instead of dates, in that case you sould use Date#() to convert to real dates.
GPT seems not to be very smart because the suggestions are misleading. Qlik doesn't execute any sql else it transfers the statement to the data-base and received a result data-set. The only thing what happens on the Qlik side is a data-interpretation if the content could be interpreted numerically in regard of the applied interpretation-variables (if none are specified it used the region-settings of the machine) and if not it's a string.
The above hinted missing of any interpretation is rather seldom and I experienced it never during a simple sql query. Therefore I think it's quite unlikely in this scenario.
I believe that a much more likely reason is that the used driver is the cause and not capable to handle the data / data-types properly. Take a look if the driver is really suitable and supported from the data-base and the used data-types and/or if any configurations could be made.
Another attempt may be to implement a cast-statement into the sql query - maybe transforming the dates into pure numbers.
