All board posts in Teradata Community

How to create a table in my data lab?

mikefiorelli — Tue, 26 Jun 2018 17:37:40 GMT

hi, so i exported 3 similar CSV files from my sharedrive to my data lab in Teradata.after exporting all 3 csv files to my data lab, i merged the three files using a union function

select "column1","column2","column3" from "datalab"." table1"

union

select "column1","column2","column3" from "datalab"." table2"

union

select "column1","column2","column3" from "datalab"." table3";

now i like to create that table and insert it into my data lab and call that table "COMBINED TABLE".

How do i create or insert that table in my data lab? my data lab is called "datalab"

Thanks

Re: Same value across multiple columns

Waldar — Tue, 26 Jun 2018 17:27:17 GMT

You could use the MAX window function :

select EmpID
     , ItemsPurchased
     , EmailID
     , max(EmailID) over(partition by EmpId) as EmailID_populated
  from MyTable;

How to find the application from which a query was submitted

mave18 — Tue, 26 Jun 2018 17:06:31 GMT

Hello,

I want to find the application from which a particular query was submitted by a user using the PDCRINFO.DBQL* tables. For instance I would want to know if a query was submiited by a user from SQL Asst, or from Business Objects, or from Informatica, or from simply a bteq script on unix etc.

I know how to locate a query in history from PDCRINFO.DBQLOGTBL_HST.

However, any pointers to the above requirement would be extremely helpful.. I am using Teradata v14

Thanks in advance...

Re: Find last match in a column return to value in the next row

GJColeman — Tue, 26 Jun 2018 17:00:29 GMT

There is something missing here - the sequence of events within an ID. Time is not sufficient because for example ID 'A' has two 22's and a 33 following the 44 in time sequence. Which is the one you are looking for?!?

Re: Find last match in a column return to value in the next row

CC — Tue, 26 Jun 2018 14:02:32 GMT

Hi GJ,

Thanks for the reply. The command you have here will return multi-rows for each ID. I need only one row for each ID - the row next to the last '44' event... Any thoughts? Thanks.

Re: Find last match in a column return to value in the next row

CC — Tue, 26 Jun 2018 14:01:24 GMT

Hi Waldar,

Thanks for the detail. Time is different for each ID.

I am new to TD SQL, the command you have here will return multi-rows for each ID. I need only one row for each ID - the row next to the last '44' event.

Could you please help? Thanks in advance.

Re: tdc.deploy.exe

Eric — Tue, 26 Jun 2018 12:45:23 GMT

The new document TDVM Getting Started Guide v3.02 contains all te power CLi commands, that 's enough for me

Re: Same value across multiple columns

Phoenix — Tue, 26 Jun 2018 09:08:05 GMT

Am sorry, its C14 only - typo..

We have only c12 and c14 here.

Re: Same value across multiple columns

dnoeth — Tue, 26 Jun 2018 07:58:54 GMT

Why should gfx.rfg@yahoo.com be assigned to C14, it's the email of C15?

Re: Predicted confidence of DenseSVMPredictor

margarete — Tue, 26 Jun 2018 07:16:38 GMT

I am sorry. I do not have these details. In case you are a Teradata or Aster customer, you can open an incident. Customer support will try reaching out to engineering to gather these details. I am not sure whether all answers can be provided but it is worth a try.

Same value across multiple columns

Phoenix — Tue, 26 Jun 2018 07:13:24 GMT

Table structure as below:

EmpID ItemsPurchased EmailID

C12 Bread abc.xyz@gmail.com

C12 Milk

C12 Butter

C14 Jam

C14 Choc

C15 Watch gfx.rfg@yahoo.com

I would need email ids to populated partitioned by empid. ie., abc.xyz@gmail.com should be populated for all rows of C12 and similiarly, gfx.rfg@yahoo.com for C14 empid.

Re: trying to get decimal precision on date difference

Waldar — Mon, 25 Jun 2018 15:15:56 GMT

I'll go with my own guess and suggest you this code :

select months_between(date1, date2) / 12.00 AS AGE_AT_CLM_LN_DT

Re: index for case statements

Waldar — Mon, 25 Jun 2018 14:48:44 GMT

For performance you must avoid the cross join + distinct operations.

One good method would be an UNPIVOT-like query :

select 'A', col1 from stg_table union all
select 'B', col2 from stg_table

You can generate this query against your ref_table, with something like :

select 'select ''' || val || ''', '
    || case val when 'A' then 'col1' when 'B' then 'col2' end
    || ' from stg_table '
    || case row_number() over(order by val desc) when 1 then ';' else ' union all ' || chr(10) end as req
  from ref_table 
 where val in ('A', 'B');

Re: Weekly Active Users using Windowing function

Waldar — Mon, 25 Jun 2018 14:00:59 GMT

The flaws of your statement is when you have missing days for an UID, the ROWS clause inside the window function is just counting number of rows backward : if you have a connection every 5 days, ROWS 7 PRECEDING will go up to 35 days of history.

In Ansi SQL you could use the RANGE operator inside your window function instead of ROWS... But it's not yet implemented inside TD.

You should generate a row for every day / users, so you can use ROWS phrase :

with cte_all_data (start_dt, uid) as
(
    select cal.start_dt
         , usr.uid
      from ica.ica_calendar_ref                       as cal
cross join (select uid from <tablename> group by uid) as usr
 left join <tablename>                                as tab  on tab.rptg_dt = cal.start_dt
                                                             and tab.uid     = usr.uid
   qualify max(tab.uid) over(partition by usr.uid order by cal.start_dt rows 7 preceding) is not null
)
  select start_dt
       , count(uid)
    from cte_all_data
group by start_dt
order by start_dt;

But I don't think it would be faster than your first solution.

Re: Find last match in a column return to value in the next row

Waldar — Mon, 25 Jun 2018 13:29:10 GMT

Qualify min(Event) over(rows between 1 preceding AND 1 preceding) = 44

Very neat, it lacks a bit of semantics for OP :

min(Event) over(partition by Id order by Col_time rows between 1 preceding and 1 preceding) = 44

, please not that in 16.x you can use the LAG function :

lag(Event) over(partition by Id order by Col_time asc) = 44

Also from your dataset, you have to specify some rules for multiples candidates : you have several rows with Event 44 at same time per Id.

Keep in mind that TIME is a reserved word, that you shouldn't use as a column name inside your database. I guess it's just for the example here.

Teradata views connection into Tableau Desktop

mellowluca — Mon, 25 Jun 2018 12:55:40 GMT

Hello,

I am trying to connect via tableau desktop to several view databases set up in a teradata environment. The view structure architecture follows these general guidelines base tables --> view off of base tables 1 --> view off of base tables 2.

I will need to connect to view 1 and view 2 layers, but it doesn't seem the this is supported by Tableau. I'm posting here to see if anyone has had experience connecting to teradata views in tableau.

Thanks!

Weekly Active Users using Windowing function

ananyak — Mon, 25 Jun 2018 08:29:07 GMT

I have rpt_date, user_id.

I am trying to find Weekly Active Users.

I get the right numbers without using windowing function:

SELECT a.start_dt, COUNT(DISTINCT uid) AS wau FROM (SELECT start_dt FROM ica.ica_calendar_ref --teradata system calendar ) a, (SELECT uid, rptg_dt FROM <tablename> ) b WHERE rptg_dt BETWEEN start_dt - 6 AND start_dt GROUP BY 1 ORDER BY 1;

I am using this query to get the same output using Windowing function, but the numbers are not correct. THis is giving me DAU and not WAU.

SELECT RPTG_DT, COUNT(DISTINCT UID) AS WAU FROM (SELECT UID, RPTG_DT, CASE WHEN SUM(UID) OVER (PARTITION BY UID ORDER BY RPTG_DT ROWS 7 PRECEDING) >= 1 THEN 1 END AS ACTIVE_OR_NOT FROM ) A WHERE ACTIVE_OR_NOT = 1 GROUP BY 1 ORDER BY 1;

What am I doing wrong...

Re: Predicted confidence of DenseSVMPredictor

berlindahermsen — Mon, 25 Jun 2018 07:21:39 GMT

Thanks for your reply. We still have some questions, hopefully you will be able to answer them as wel.

So the predict_confidence is not done by Platt scaling ( p(y|X) = 1 / (1 + exp(A * f(X) + B)) ) as done in the aforementioned paper?
Is there a way to extract the internal prediction?
And is the internal prediction equal to the signed distance of a sample from the hyperplane?

Thanks!

Re: Trimming specific fields

JackJack — Mon, 25 Jun 2018 04:36:51 GMT

Thank you very much, GJColeman

Re: Processing multiple files from S3 using Teradata TPT

herwin — Sun, 24 Jun 2018 02:45:37 GMT

Hi,
I have problem exporting Teradta data to multiples files on S3 using TPT.
My settings: varchar accessmoduleinitstr = 'S3Bucket=bucketname S3Prefix= prefixname/stores/ S3Object= store S3SinglePartFile=False'
and
APPLY TO OPERATOR(file_writer[3])
select * from OPERATOR(export_item[1]);
My tbuild has the -C option as well.
The display did say: Total files processed:3
But I only got one file (F00000) in the s3 "store" folder and the file size is 1/3 of the total file size if not split. Not sure where the other two files went.

Thanks