- Go to the specific directory where files are stored.
- Select all files by CNTRL+A.
- Press Shift and Right click on files
- Select “copy as path” from right click menu
- Paste the result in file editor
Thursday, 2 December 2021
List file names in a directory by file explorer
VLOOKUP - Data Cleansing - MSExcel
=IF(ISNA(VLOOKUP($B3,G$3:G$26,1,FALSE)), "Not Present", "Present")
=IF(ISNA(VLOOKUP($D3,B$3:B$27,1,FALSE)), "Not Used", "Used")
Sunday, 19 September 2021
Create Sequence in tables
Thursday, 26 August 2021
Java Script - Replace forward slash with backward slash
str = get_instal_path_org;
res = str.replace(/\//g, "\\");;
Monday, 23 August 2021
AWS - Useful commands
LINUX Command to connect AWS using KEYS,
export AWS_ACCESS_KEY_ID=AKIAAAAUQG7QC2O4KS6NKGSM;
export AWS_SECRET_ACCESS_KEY=I1DCBC3nwsRcV8+aoXl0c/PX4iFsTJMr1aZ8EzIDKGSMS;
aws s3 ls s3://kgsm/active/
s3://kgsm/raw
s3://kgsm/active
WINDOWS Command to connect AWS using KEYS,
setx AWS_ACCESS_KEY_ID AKIAV7UQG7Q7C2O4KS6NKGSM
setx AWS_SECRET_ACCESS_KEY I1DCBC3nwsRcV8+aoXl0c/PX4iFsTJMr1aZ8EzIDKGSMS
setx AWS_DEFAULT_REGION us-east-1
aws s3 ls s3://kgsm/active/
WINDOWS Command to CREATE NEW FOLDER IN AWS and UPLOAD THE FILE
aws s3 cp F:\run.bat s3://kgsm/active/a1/
Tuesday, 17 August 2021
Postgresql : Generate token
CREATE TABLE adempiere.license_tomato_app
(
customerid integer,
name text COLLATE pg_catalog."default",
location text COLLATE pg_catalog."default",
key text COLLATE pg_catalog."default" DEFAULT upper(substr(md5((random())::text), 0, 50)),
plan text COLLATE pg_catalog."default",
license_start_date timestamp without time zone,
license_end_date timestamp without time zone,
last_sync_data_time timestamp without time zone,
industry_type character varying(50) COLLATE pg_catalog."default",
business_name character varying(50) COLLATE pg_catalog."default",
isactive integer DEFAULT 1,
created_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP,
updated_at timestamp without time zone,
created_by integer,
updated_by integer
)
WITH (
OIDS = FALSE
)
TABLESPACE pg_default;
Wednesday, 28 July 2021
Getting SELECT to return a constant value even if zero rows match - Postgresql
SELECT COALESCE(status,'not a valid token') AS status,last_sync_data_time FROM
(
SELECT
CASE WHEN CURRENT_TIMESTAMP <= license_end_date THEN 'valid' ELSE 'expired' END AS status, last_sync_data_time
FROM adempiere.license_tomato_app
WHERE key = '7EF3D9B8183D9C7AB90242AF5D011ABE'
union all
select null, '1900-01-01 00:00:00' last_sync_data_time where not exists (select 1 from adempiere.license_tomato_app WHERE key = '7EF3D9B8183D9C7AB90242AF5D011ABE')
) t
Wednesday, 7 July 2021
Tuesday, 6 July 2021
Thursday, 27 May 2021
Postgresql: Get all tables count from DB
SELECT schemaname,relname,n_live_tup
FROM pg_stat_user_tables
ORDER BY n_live_tup DESC;
Postgresql: Get all primary keys from all the tables in DB
SELECT
kcu.table_schema, kcu.table_name, tco.constraint_name, kcu.ordinal_position AS position, kcu.column_name AS key_column
FROM information_schema.table_constraints tco
JOIN information_schema.key_column_usage kcu
ON kcu.constraint_name = tco.constraint_name
AND kcu.constraint_schema = tco.constraint_schema
AND kcu.constraint_name = tco.constraint_name
WHERE tco.constraint_type = 'PRIMARY KEY'
ORDER BY
kcu.table_schema, kcu.table_name, position;
Columns
table_schema - PK schema name
table_name - PK table name
constraint_name - PK constraint name
position - index of column in table (1, 2, ...). 2 or higher means key is composite (contains more than one column)
key_column - PK column name
Wednesday, 19 May 2021
Postgresql: Find the 'N' th occurrences in a string
Sample Table:
create table filter_table (filter_type varchar(500));
Insert Query:
insert into filter_table (filter_type) values ('1111111 > 22222 > 3333333> 4444444> 123456789 > abcdefghi > 987654321 > zyxwvuts > 0000000 >');
Query to find the occurrences:
select substring(filter_type from '^(([^ ]* ){2})')
from filter_table
DBT - Models
Models are where your developers spend most of their time within a dbt environment. Models are primarily written as a select statement and ...
-
Upon using the custom query in the source object, the mapping fails with the following error: The select query for read operation failed bec...
-
Data mining is the process of finding patterns from large data sets and analyzing data from different perspectives. It allows business use...