Scribd Logo
Upload

Welcome to Scribd!

  • 71 views

    Data Stage Scenarios: Scenario1. Cummilative Sum

    Uploaded by

    abreddy2003
    Scenario 1 describes how to calculate a cumulative sum by creating a stage variable and adding values to it. Scenario 2 explains how to find the highest and lowest salaries by department. Scenario 3 splits non-key columns across two targets while keeping the key column in both.

    Copyright:

    © All Rights Reserved
    Download as DOCX, PDF, TXT or read online from Scribd

    Data Stage Scenarios: Scenario1. Cummilative Sum

    Uploaded by

    abreddy2003
    71 views13 pages
    Scenario 1 describes how to calculate a cumulative sum by creating a stage variable and adding values to it. Scenario 2 explains how to find the highest and lowest salaries by department. Scenario 3 splits non-key columns across two targets while keeping the key column in both.

    Original Title

    Ds Scenrio

    Copyright

    © © All Rights Reserved

    Available Formats

    DOCX, PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    Scenario 1 describes how to calculate a cumulative sum by creating a stage variable and adding values to it. Scenario 2 explains how to find the highest and lowest salaries by department. Scenario 3 splits non-key columns across two targets while keeping the key column in both.

    Copyright:

    © All Rights Reserved
    Download as DOCX, PDF, TXT or read online from Scribd
    Download as docx, pdf, or txt
    71 views13 pages

    Data Stage Scenarios: Scenario1. Cummilative Sum

    Uploaded by

    abreddy2003
    Scenario 1 describes how to calculate a cumulative sum by creating a stage variable and adding values to it. Scenario 2 explains how to find the highest and lowest salaries by department. Scenario 3 splits non-key columns across two targets while keeping the key column in both.

    Copyright:

    © All Rights Reserved
    Download as DOCX, PDF, TXT or read online from Scribd
    Download as docx, pdf, or txt
    of 13

    Data stage Scenarios:

    Scenario1. Cummilative Sum:

    Create stage variable VCUMMSUM and assign initial value=0

    VCUMMSUM=VCUMMSUM + DSLink3.SAL
    2. EACH DEPT WISE HIEGHST AND LOWEST SAL REQUIRED

    Output

    3.Split the non-key columns to separate tables with key column in both  

    Source:
    Target:T1

    Target:T2

    JOB:

    Job Logic:

    Copy stage

    T1: ID,NAME

    T2:ID,PHONENO
    4,Converting Rows into Columns without using pivot:

    Source:

    Target:

    Job Design:

    Job Logic:

    In Transformer Change the Field Name MONTH1 to MONTH Similary for MONTH2,MONTH3

    And copy Three Transformer output to Funnel

    5.Fetch First and Last record from a file

    Source:

    Target:
    Job Design:

    Job Logic: Take one record from head and take one record from Tail and club these two at target

    Scenario 6: How to send first half record to target

    Source:

    Target:
    JOB:

    Job Logic:In source DB write the query given below

    select * from emp where rownum <= ( select count(*)/2 from emp)

    Scenario 7 How to send second half record to target:

    Source:

    Target:
    Job Design:

    Job Logic:

    in source DB write the query given below

    Select * from emp minus Select * from emp where rownum <= ( select count(*)/2 from emp)

    Scenario 8: How to send alternate record to target:

    Source:
    Scenario 8: How to send Even record in to one target &Odd records in to another target:

    Source:

    Target:

    Even:

    Odd:

    Job Design
    JobLogic:

    Create Stage variable with name Flag and enter following derivation

    If Mod(DSLink2.EMPNO,2)=0 Then "EVEN" ELSE "ODD"=FLAG

    And in Transformer Constraints Even link apply the condition FLAG="EVEN" Similarly for Odd
    constraint apply the condition FLAG="ODD"

    Scenario 9: In Emp table there are three departments (dept no 30,20,10). Separate the record
    to different target department wise.

    Source:

    Target1:

    Target2:

    Target3:
    Job Design:

    Job Logic:

    In transformer constraint apply the below conditions

    Scenario10: How to load every nth row from a Flat file/ relational DB to the target? Suppose
    n=3, then in above condition the row numbered 3,6,9,12,....so on, This example takes every 3
    row to target table.

    Source: Emp table has 14 records out of 14 records it needs to be load 4 records in to target

    Method1:Use sample stage and operate in period mode set the properties given below

    Samplemode=period
    Perpartition=3

    Target:

    Method2:

    Jobdesign:

    Job Logic: Create stage variable with CNT and assign initial value zero and write expression in
    derivation part

    CNT=CNT+1

    And in transformer constraint apply the condition given below

    Mod(StageVar,3)=0

    Target:
    Scenario11:In source  there are some record. Suppose I want to send three targets. First record
    will go to first target, Second one will go to second target and third record will go to third
    target and then 4th to 1st,5th to 2nd , 6th to   3rd and so on.

    Jobdesign:

    Logic:

    In column generator create new column ie seqno and in extended column properties apply the
    condition given below

    Generator:

    Type=Cycle

    Intialvalue=1

    Increment=1

    Max=3

    So now the seqno value generate like 1,2,3,1,2,3- -so like next in transformer for three targets apply
    the condition which is given below

    Target1= DSLink4.seqno=1

    Target2= DSLink4.seqno=2

    Target3= DSLink4.seqno=3

    Scenario12:Previous Sal

    You might also like