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
*****
Showing posts with label BODI SCENARIOS. Show all posts
Showing posts with label BODI SCENARIOS. Show all posts
Monday, January 5, 2009
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
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.
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.
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);
$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}
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
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
******************************************
$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
******************************************
Subscribe to:
Posts (Atom)