Showing posts with label BODI SCENARIOS. Show all posts
Showing posts with label BODI SCENARIOS. Show all posts

Monday, January 5, 2009

Loading the data from a table to the XML TARGET

The data looks like this in the source table :
ID NAME ADD1 STREET1 CITY1 COUNTRY1 ADD2 STREET2 CITY2 COUNTRY2
1 SUDHEER 216 SHIPRA GZB INDIA C-701 JAIPURIA GZB INDIA2 ABHISHEK 275 DWARKA DELHI INDIA

The output structure should be

ID NAME ADDRESS

In the ADDRESS you will find the ADD,STREET,CITY (AS MANY AS ARE THEY Eg : ADD1,ADD2 in the above eg : It can be ADD1,ADD2,ADD3,ADD4)

Sol :

FLOW :

SOURCE TABLE --- QUERY1--PIVOT--- QUERY2---QUERY3--- QUERY4---XMLTARGET

1)QUERY1--* Simply Extracting the data from the Source Table.

2)PIVOT---* Converting the Columns to rows

4 PIVOTAL SETS

1st Set---* ADD1, ADD2
2nd Set---* STREET1,STREET2
3rd Set---* CITY1,CITY2
4th Set---* COUNTRY1,COUNTRY2

Name the DATA FIELD COLUMNS as ADD,STREET,CITY,COUNTRY

3)QUERY3--* Make the XML Structure you are in need of

4)QUERY4--*For the Parent tree select DISTINCT ROWS option

In order to restrict DUPLICATE data.

FOR THE ADDRESS heirarchy place the following condition in the
where condition.

(((((Xml_Struct.ID = Xml_Struct.Pivot.ID) AND
(Xml_Struct.Pivot.ADD_DATA IS NOT NULL )) AND
(Xml_Struct.Pivot.STRT_DATA IS NOT NULL )) AND
(Xml_Struct.Pivot.CITY_DATA IS NOT NULL )) AND
(Xml_Struct.Pivot.CNTRY_DATA IS NOT NULL ))

In order to restrict NULL data

In the Group By tab place the following :
(((((Xml_Struct.ID = Xml_Struct.Pivot.ID) AND
(Xml_Struct.Pivot.ADD_DATA IS NOT NULL )) AND
(Xml_Struct.Pivot.STRT_DATA IS NOT NULL )) AND
(Xml_Struct.Pivot.CITY_DATA IS NOT NULL )) AND
(Xml_Struct.Pivot.CNTRY_DATA IS NOT NULL ))

In order to restrict DUPLICATE data.

5)QUERY5---*Construct the XML structure for NRDM column

Download ATL : http://www.divshare.com/download/6248312-2ed

*****

Monday, December 29, 2008

Using a Table Without Importing It

In SQL Transform :

1)Create Output Columns that are required by seeing the table structure in the backend.

2)Do not import the table in the respective DataStore(The Schema i.e Connected with in which the table is available).

3)Select the appropriate DataStore in which the actual table is residing in the Backend but not in the datastore.

4)Write the appropriate SQL Query as per the columns created.

5)If you want, you can use the global variable which represent the respective schema name.

6) Do not click on the Update Schema.

7)Implement the Transformation Logic i.e required in the job.

8)Execute the job

DOWNLOAD ATL : http://www.divshare.com/download/6200214-d42

Sunday, December 28, 2008

Column having muliple values separated with ATTHERATE symbol

(b)DATA RECEIVED VIA FLATFILE (STRUCTURE)
Header :
REGNO,MAKE,YEAR ,DATES LEASED FROM,DATES LEASED TO,CUSTID

Ist Row :
SV53AYC,FORD,2003,28/10/06@22/10/06@12/10/06,30/10/06@27/10/06@20/10/06,HK113@TU141@BK121

IInd Row Data :

SV54KGH,HONDA,2004, 24/08/06@19/8/06, 28/08/2006@22/08/2006, BB141@TM186

IIIrd Row Data :

SV07XKY, TOYOTA, 2007, , ,

Iv Row Data :

SV06VWY,BMW, 2006, 11/03/07, 23/03/07, MK129

Plz Remove Header :, Ist Row Data :, II Row Data :, III Row Data :, Iv Row Data

Solution :





Downloadable Atl Link :
------------------------

http://www.divshare.com/download/6199434-52c

Contains Source CSV file ...and .....Solution ATL file.

Wednesday, December 17, 2008

Renaming the Datastores of Soure / Target / Intermediate Tables

In order to Rename the Datastores of Source / Target / Intermediate Tables.

1) Take the atl of the job / jobs / Dataflows / Workflows / Datastores

2)Open in Textpad / Notepad.

3) Thru Find & Replace rename the Datastores of the desired one.

4) Save and Import the modified atl into the Repository.

Tuesday, December 16, 2008

Extract Only the File Name from the relevant/Absolete Path

$G_FILENAME = 'C:\RAMEHS\PRINCE\KDKD.TXT';

$G_VAR1 = word_ext($G_FILENAME,-1,'\\');

PRINT('Filename Is...... '||$G_VAR1);

Strings Embedded with Commas enclosed with " "

Flat File... Comma Delimiter....Strings Embedded with Commas enclosed with " "....

Eg :

Id,Name,Address,Amt
123,"Ramesh Babu","2/16,KAD Road,GZB",50000



Solution :

In the Delimiters Section of Flat File Definition Set the properties like :


Column....... ,
Row..........{New Line}
Text.........." (By default this is None}

Thursday, December 11, 2008

Dataflow In a Infinite Loop searching for any Flat file Existance.

Job Is executed 1 time.

Check for Any Flat file(Column structure will be same) exists in "X" Folder.

Name of the flat file changes every time. But the Column Structure will be same.

If any Flat file exists then Dataflow will be executed.

Once the data is loaded into the target table the Source flat file in "X" folder should be deleted.

Dataflow will be in a Infinite Loop searching for any Flat file Existance.


Place a Script & fill the following code:

$G_Stop =1;

Place a While Loop Icon i.e available on the right hand side slider:

put the following condition :

($G_Stop = 1)

Inside the While Loop :

Place a Script & fill the following code :

exec('D:\Flat\b.bat','',2);

$G_a =file_exists('D:\zz.txt');


Code of b.bat :

cd\

d:

cd d:\flat

if not exist *.txt del d:\zz.txt

if exist *.txt dir > d:\zz.txt

Place a Conditional Icon next to the Script :

Put the following condition :

($G_a=1)

Inside the If Condition :

Place the Dataflow which u want to execute.

Next to it place a script and fill the following code.

exec('D:\Flat\Del_Flat.bat','',2);

Code of Del_Flat.bat :

cd\
d:
cd d:\flat
del *.txt

--------------------------------------------

Download Atl :

http://www.divshare.com/download/6200214-d42

Thursday, December 4, 2008

Job Failed----Error Log should be printed automatically.

Script In BODI :

$G_PRINT = replace_substr(get_error_filename(),' ','*');
$G_PRINT2 = word_ext($G_PRINT, 8, '/');
$g_print3 = substr($g_print,1,index( $g_print,'/error_',1)-1);
exec('c:\a1.bat', $g_print3||' '||$g_print2 , 8);

Batch File Script
c:
cd\
cd %1
cd\

type %2 > c:\echo11.txt
lpr -S 192.168.10.99 -P M3027 -J error_logs c:\echo11.txt

=======================================

Other commands that can be written in Batch file Script
For copying the error file to other folder
------------------------
copy %2 c:\echo11.txt

type %2 > c:\echo11.txt

For Appending the data of 1 file in another
---------------------
type echo11.txt >> abc.txt




******************************************