10 Replies Latest reply on Sep 27, 2021 5:00 AM by aliva dash

    Using informatica transformation for record count

    aliva dash Active Member

      I have a table with below format:

      source1:

       

      Profile_nameDimensionRule1Rule2
      P1AP11
      P1AP01
      P1AP11
      P1NA01
      P1LA10
      P1LA10

       

      Required output (target1):

       

      RECORD_COUNTPROFILE_NAMEDIMENSIONRULENAMERULEVALUEDATE
      1P1APRule10current_date
      2P1APRule11current_date
      2P1LARule11current_date
      1P1NARule10current_date
      3P1APRule21current_date
      1P1NARule21current_date
      2P1LARule20current_date

       

       

      I am to get the required output if i use sql query:

       

      create or replace table  target1"(

      select * from (

      select count(*) as record_count, profile_name ,dimension,rulename,rulevalue ,current_date as date from (

      select profile_name ,dimension ,rulename,  rulevalue

      from source1

      unpivot

      (

        rulevalue

        for rulename in

        ("Rule1","Rule2")

      ))

      group by profile_name ,dimension,rulename,rulevalue)

      );

       

      can this be implemented using informatica transformation. Please help.