Format the uncleaned data set

[sriram____777]

Hi Everyone

My dataset looks like this

Segment>>	Consumer				Consumer Total	Corporate
Ship Mode>>	First Class	Same Day	Second Class	Standard Class		First Class
Order ID
CA-2011-100293
CA-2011-100706			129.44		129.44
CA-2011-100895				605.47	605.47
CA-2011-100916
CA-2011-101266			13.36		13.36

How to convert the above table to below format
Is it Possible?

Segment	Ship Mode	OrderID	Sales
Consumer	First Class	CA-2011-103366	149.95
Consumer	First Class	CA-2011-109043	243.6
Consumer	First Class	CA-2011-113166	9.568
Consumer	First Class	CA-2011-124023	8.96

[rwunderlich]

I don't see a relationship between the data in the first table and the second table? For example I see OrderId "CA-2011-103366" in table 2 but not in table 1.  Are they meant to be mapped or did you use different data?

-Rob

[sriram____777]

I just put the sample data it should be mapped 

That OrderId "CA-2011-103366" also present in the table 1 but here  I'm just showing the sample

[marksouzacosta]

Hi @sriram____777 ,

Will be helpful if you do an expected table output based on your sample data, preferably covering all possible scenarios, otherwise will be quite hard to figure out what you are looking for.

Your data format is also very unusual. Can you please share a sample of your file?

Regards,

Mark Costa

[sriram____777]

I have aatacehed sample data set please check

[lennart_mo · Accepted solution]

H @sriram____777,

this is the script i came up with based on your sample:

CLEAN_DATA_TMP:
CrossTable("Ship Mode",Sales,2) LOAD
'Customer' as Segment,
"Ship Mode>>" as OrderID,
"First Class",
"Same Day",
"Second Class",
"Standard Class"
FROM [lib://AttachedFiles/Data Sample.xlsx]
(ooxml, embedded labels, header is 1 lines, table is [Dirty 1])
Where "Ship Mode>>"<>'Order ID'
;

CrossTable("Ship Mode",Sales,2) LOAD
'Corporate' as Segment,
"Ship Mode>>" as OrderID,
"First Class1" as "First Class",
"Same Day1" as "Same Day",
"Second Class1" as "Second Class",
"Standard Class1" as "Standard Class"
FROM [lib://AttachedFiles/Data Sample.xlsx]
(ooxml, embedded labels, header is 1 lines, table is [Dirty 1])
Where "Ship Mode>>"<>'Order ID'
;

CrossTable("Ship Mode",Sales,2) LOAD
'Home Office' as Segment,
"Ship Mode>>" as OrderID,
"First Class2" as "First Class",
"Same Day2" as "Same Day",
"Second Class2" as "Second Class",
"Standard Class2" as "Standard Class"
FROM [lib://AttachedFiles/Data Sample.xlsx]
(ooxml, embedded labels, header is 1 lines, table is [Dirty 1])
Where "Ship Mode>>"<>'Order ID'
;

CLEAN_DATA:
NoConcatenate
Load
Segment,
"Ship Mode",
OrderID,
Sales
Resident CLEAN_DATA_TMP;

Drop Table CLEAN_DATA_TMP;

 I hope this gives you your desired output!

The resident load is only there to reorder the fields in the backend, so it is optional.

[sriram____777]

Great Thanks @lennart_mo