LAE

Welcome to the LAE community!  Please feel free to start a discussion in the discussion tab or join in a conversation.

Discussions

Members

Resources

Events

 View Only

  • 1.  Changing data format - parsing data

    Employee
    Posted 01-28-2016 09:12

    Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

    Originally posted by: mw9286

    Hello All:

    I current have data in Excel in the following format. I want to parse the data to resemble the desired format.

    Thank you

    CURRENT FORMAT
    ban ST0035 ST0037 ST0036 ST0038 SH0039
    123456789 -80 -26 -18 -10 -47
    987654321 -50 -26

    DESIRED FORMAT
    ban promo amt
    123456789 ST0035 -80
    123456789 ST0037 -26
    123456789 ST0036 -18
    123456789 ST0038 -10
    123456789 SH0039 -47
    987654321 ST0037 -50
    987654321 ST0036 -26


  • 2.  RE: Changing data format - parsing data

    Employee
    Posted 01-28-2016 09:34

    Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

    Originally posted by: belliveaujd

    Copy and paste the following into your data flow to see an example of how to make use of the Pivot Names to Data node:


    node:Static_Data
bretype:core::Static Data
editor:sortkey=56aa427712a44c3b
output:@40fe6c55598828e5/=
prop:StaticData=<<EOX
ban, ST0035, ST0037, ST0036, ST0038, SH0039
123456789, -80, -26, -18, -10, -47
987654321,, -50, -26,,
EOX
editor:XY=150,90
end:Static_Data

node:Pivot__Names_To_Data
bretype:core::Pivot - Names To Data
editor:sortkey=56aa43196c556340
input:@4c8fdfd712226f21/=Static_Data.40fe6c55598828e5
output:@4c93bf5c2ec21c56/=
prop:DataOutputField=amt
prop:NameOutputField=promo
prop:PivotFields=SH0039,ST0035,ST0036,ST0037,ST0038
editor:XY=270,90
end:Pivot__Names_To_Data

node:Split_Null_Amounts
bretype:core::Split
editor:Label=Split Null Amounts
editor:sortkey=56aa439517055dab
input:@40fd2c74167f1ca2/=Pivot__Names_To_Data.4c93bf5c2ec21c56
output:@40fd2c7420761db6/=
output:@456df11556bd6bcf/=
prop:PredicateExpr=<<EOX
'amt' != ""
EOX
prop:Script=<<EOX
emit ban, promo, amt
EOX
editor:XY=370,90
end:Split_Null_Amounts


  • 3.  RE: Changing data format - parsing data

    Employee
    Posted 01-29-2016 07:17

    Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

    Originally posted by: awilliams1024

    Thanks a lot Josh for posting the solution.
    One additional point: to simplify configuration of the Pivot Names to Data node you could also have used a wildcard in the PivotFields parameter (with a value of S* in this case)


  • 4.  RE: Changing data format - parsing data

    Employee
    Posted 01-29-2016 07:51

    Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

    Originally posted by: belliveaujd

    Originally posted by: awilliams1024
					

    Thanks a lot Josh for posting the solution.
    
One additional point: to simplify configuration of the Pivot Names to Data node you could also have used a wildcard in the PivotFields parameter (with a value of S* in this case)
    Great point Adrian! I completely missed that. Here's an update, using a wildcard to pivot on the SH0* and ST0* fields

    node:Static_Data
bretype:core::Static Data
editor:sortkey=56aa427712a44c3b
output:@40fe6c55598828e5/=
prop:StaticData=<<EOX
ban, ST0035, ST0037, ST0036, ST0038, SH0039
123456789, -80, -26, -18, -10, -47
987654321,, -50, -26,,
EOX
editor:XY=150,90
end:Static_Data

node:Pivot__Names_To_Data
bretype:core::Pivot - Names To Data
editor:sortkey=56aa43196c556340
input:@4c8fdfd712226f21/=Static_Data.40fe6c55598828e5
output:@4c93bf5c2ec21c56/=
prop:DataOutputField=amt
prop:NameOutputField=promo
prop:PivotFields=SH0*,ST0*
editor:XY=270,90
end:Pivot__Names_To_Data

node:Split_Null_Amounts
bretype:core::Split
editor:Label=Split Null Amounts
editor:sortkey=56aa439517055dab
input:@40fd2c74167f1ca2/=Pivot__Names_To_Data.4c93bf5c2ec21c56
output:@40fd2c7420761db6/=
output:@456df11556bd6bcf/=
prop:PredicateExpr=<<EOX
'amt' != ""
EOX
prop:Script=<<EOX
emit ban, promo, amt
EOX
editor:XY=370,90
end:Split_Null_Amounts