How to convert string values to row wise data? | Community
Skip to main content
A
aagk123
Level 5
August 24, 2023
Solved

How to convert string values to row wise data?

  • August 24, 2023
  • 1 reply
  • 1527 views

I want to convert the string values collected in evar as table format as shown below. How to do it?

 

 

This post is no longer active and is closed to new replies. Need help? Start a new post to ask your question.
Best answer by Jennifer_Dungan

So you have a single eVar passing multiple values ("inStock,inStock,outOfStock,outOfStock,outOfStock") which is associated to a single Revenue value? And you want to split and display only the unique values in the string ("inStock" or "outOfStock") as rows, but both associated to the same revenue value??

 

Is this a one time thing? Or does this need to be automated?

 

For hypothetical, I am going to assume automated, so let's say your value of eVar117 is in cell A1 in excel

 

You can use this formula to split the list into sorted and unique values:

=SORT(UNIQUE(TEXTSPLIT(TEXTJOIN(",",,A1),,",",TRUE,0)))

 

So essentially:

 

Now, you could set up in the next column (H), something like 

=IF(H1<>"", $F$1, "")

 

As you copy the formula down, it will become "H1" the "H2" then "H3", etc, but "F1" will be locked, so:

 

 

 

But, I wonder if there is a better way to optimize your analytics so that you can use the data without all this manipulation, and having appropriate Revenue associated to each item.

1 reply

Jennifer_Dungan
Community Advisor and Adobe Champion
Jennifer_DunganCommunity Advisor and Adobe ChampionAccepted solution
Community Advisor and Adobe Champion
August 24, 2023

So you have a single eVar passing multiple values ("inStock,inStock,outOfStock,outOfStock,outOfStock") which is associated to a single Revenue value? And you want to split and display only the unique values in the string ("inStock" or "outOfStock") as rows, but both associated to the same revenue value??

 

Is this a one time thing? Or does this need to be automated?

 

For hypothetical, I am going to assume automated, so let's say your value of eVar117 is in cell A1 in excel

 

You can use this formula to split the list into sorted and unique values:

=SORT(UNIQUE(TEXTSPLIT(TEXTJOIN(",",,A1),,",",TRUE,0)))

 

So essentially:

 

Now, you could set up in the next column (H), something like 

=IF(H1<>"", $F$1, "")

 

As you copy the formula down, it will become "H1" the "H2" then "H3", etc, but "F1" will be locked, so:

 

 

 

But, I wonder if there is a better way to optimize your analytics so that you can use the data without all this manipulation, and having appropriate Revenue associated to each item.

Jennifer_Dungan
Community Advisor and Adobe Champion
Jennifer_DunganCommunity Advisor and Adobe Champion
Community Advisor and Adobe Champion
August 24, 2023

If you are talking about making this display in Adobe itself, then you will need to change and optimize your tracking.

A
aagk123Author
Level 5
August 24, 2023

Yes I want to push the values to Adobe Analytics something like how we break product string values. How to achieve it? Right now this is how we get values from data layer.

Terms & ConditionsAccessibility statement

Loading footer...