Scribd
Upload
18 views16 pages

Transform Data - Start

The document contains a detailed inventory of products including their names, stores, dates of transactions, references, and profit information. It also includes lookup tables for districts and regions, as well as calculations related to dates and volumes. Overall, it serves as a comprehensive record of product sales and associated metrics across various stores.

Uploaded by

Hân Trần Gia
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as XLSX, PDF, TXT or read online on Scribd
18 views16 pages

Transform Data - Start

The document contains a detailed inventory of products including their names, stores, dates of transactions, references, and profit information. It also includes lookup tables for districts and regions, as well as calculations related to dates and volumes. Overall, it serves as a comprehensive record of product sales and associated metrics across various stores.

Uploaded by

Hân Trần Gia
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as XLSX, PDF, TXT or read online on Scribd

Product ProdName Store Date Reference StoreRef

1 Hammer A 1/11/2021 1-AX-JFY AX


1 Hammer F 3/12/2021 1-FY-DJY FY
2 Nail B 5/13/2021 2-BZ-SJY BZ
2 Nail G 8/14/2021 2-GX-MJY GX
3 Screwdriver C 1/15/2022 3-CY-JFY CY
3 Screwdriver A 2/16/2022 3-AZ-DJY AZ
4 Ladder D 4/17/2022 4-DX-SJY DX
4 Ladder I 8/18/2022 4-IY-MJY IY
5 Drill E 9/19/2022 5-EZ-JFY EZ
5 Drill J 12/20/2022 5-JX-DJY JX
RefCode
JFY
DJY
SJY
MJY
JFY
DJY
SJY
MJY
JFY
DJY
Product ProdName Store Date Reference StoreRef RefCode
1 Hammer A 1/11/2021 1-AX-JFY AX JFY
1 Hammer F 3/12/2021 1-FY-DJY FY DJY
2 Nail B 5/13/2021 2-BZ-SJY BZ SJY
2 Nail G 8/14/2021 2-GX-MJY GX MJY
3 Screwdriver C 1/15/2022 3-CY-JFY CY JFY
3 Screwdriver A 2/16/2022 3-AZ-DJY AZ DJY
4 Ladder D 4/17/2022 4-DX-SJY DX SJY
4 Ladder I 8/18/2022 4-IY-MJY IY MJY
5 Drill E 9/19/2022 5-EZ-JFY EZ JFY
5 Drill J 12/20/2022 5-JX-DJY JX DJY
Concat Prod Trim Substitute Sub
AXJFY 1|Hammer 1-AX-JFY 1|Hammer Hammer
FYDJY 1|Hammer 1-FY-DJY 1|Hammer Hammer
BZSJY 2|Nail 2-BZ-SJY 2|Nail Box of Nail
GXMJY 2|Nail 2-GX-MJY 2|Nail Box of Nail
CYJFY 3|Screwdriver 3-CY-JFY 3|Screwdriver Screwdriver
AZDJY 3|Screwdriver 3-AZ-DJY 3|Screwdriver Screwdriver
DXSJY 4|Ladder 4-DX-SJY 4|Ladder Ladder
IYMJY 4|Ladder 4-IY-MJY 4|Ladder Ladder
EZJFY 5|Drill 5-EZ-JFY 5|Drill Drill
JXDJY 5|Drill 5-JX-DJY 5|Drill Drill
Len Left Right Mid
61 Hammer AX
61 Hammer FY
42 Nail BZ
42 Nail GX
11 3 Screwdriver CY
11 3 Screwdriver AZ
64 Ladder DX
64 Ladder IY
55 Drill EZ
55 Drill JX
Product ProdName Store Date Reference StoreRef RefCode
1 Hammer A 1/11/2021 1-AX-JFY AX JFY
1 Hammer F 3/12/2021 1-FY-DJY FY DJY
2 Nail B 5/13/2021 2-BZ-SJY BZ SJY
2 Nail G 8/14/2021 2-GX-MJY GX MJY
3 Screwdriver C 1/15/2022 3-CY-JFY CY JFY
3 Screwdriver A 2/16/2022 3-AZ-DJY AZ DJY
4 Ladder D 4/17/2022 4-DX-SJY DX SJY
4 Ladder I 8/18/2022 4-IY-MJY IY MJY
5 Drill E 9/19/2022 5-EZ-JFY EZ JFY
5 Drill J 12/20/2022 5-JX-DJY JX DJY
12/31/1899
Subtract Dates Datedif Year Month eomonth
2021 1 1/31/2021
60 2021 3 3/31/2021
62 2021 5 5/31/2021
93 2021 8 8/31/2021
154 2022 1 1/31/2022
32 2022 2 2/28/2022
60 2022 4 4/30/2022
123 2022 8 8/31/2022
32 2022 9 9/30/2022
92 2022 12 12/31/2022

YearFrac 121.02778
Weekday Day of Week
1 Monday
5 Friday
4 Thursday
6 Saturday
6 Saturday
3 Wednesday
7 Sunday
4 Thursday
1 Monday
2 Tuesday
Reference Product Store Volume Profit District Region
1-AX-JFY 1A 34 1170.96 D1 North
1-FY-DJY 1E 28 695.52 D3 East
2-BZ-SJY 2B 39 999.57 D1 North
2-GX-MJY 2C 0 3748.68 D2 South
3-CY-JFY 3C 43 1698.07 D2 South
3-AZ-DJY 3A 78 139.62 D1 North
4-DX-SJY 4D 60 1879.8 D3 West
4-IY-MJY 4D 80 2828.8 D3 West
5-EZ-JFY 5F 91 861.77 #N/A #N/A
5-JX-DJY 5E 70 1101.1 D3 East
Lookup Table
Store Lookup District Region
A D1 North
B D1 North
C D2 South
D D3 West
E D3 East
1 2 3
Reference Product Store Volume Profit District Region
1-AX-JFY 1A 34 1170.96 D1 North
1-FY-DJY 1E 28 695.52 D3 East
2-BZ-SJY 2B 39 999.57 D1 North
2-GX-MJY 2C 0 3748.68 D2 South
3-CY-JFY 3C 43 1698.07 D2 South
3-AZ-DJY 3A 78 139.62 D1 North
4-DX-SJY 4D 60 1879.8 D3 West
4-IY-MJY 4D 80 2828.8 D3 West
5-EZ-JFY 5F 91 861.77 #N/A #N/A
5-JX-DJY 5E 70 1101.1 D3 East

Row

District D3
Region West
City #N/A
Region Lookup Table
North Store Lookup District Region
East A D1 North
North B D1 North
South C D2 South
South D D3 West
North E D3 East
West 1 2 3
West
#N/A
East

5
Reference Product Store Volume Profit District Region
1-AX-JFY 1A 34 1170.96 D1 North
1-FY-DJY 1E 28 695.52 D3 East
2-BZ-SJY 2B 39 999.57 D1 North
2-GX-MJY 2C 0 3748.68 D2 South
3-CY-JFY 3C 43 1698.07 D2 South
3-AZ-DJY 3A 78 139.62 D1 North
4-DX-SJY 4D 60 1879.8 D3 West
4-IY-MJY 4D 80 2828.8 D3 West
5-EZ-JFY 5F 91 861.77 #N/A #N/A
5-JX-DJY 5E 70 1101.1 D3 East
Region Store - Match District Lookup Table
North 1 D1 Store Lookup District Region
East 5 D3 A D1 North
North 2 D1 B D1 North
South 3 D2 C D2 South
South 3 D2 D D3 West
North 1 D1 E D3 East
West 4 D3 1 2 3
West 4 D3
#N/A #N/A #N/A
East 5 D3
1 2 3
1A D1 North
2B D1 North
3C D2 South
4D D3 West
5E D3 East
6 Err:502 Err:502 Err:502
Reference Product Store Volume Profit Price District
1-AX-JFY 1A 34 1170.96 34.44 D1
1-FY-DJY 1E 28 695.52 24.84 D3
2-BZ-SJY 2B 39 999.57 25.63 D1
2-GX-MJY 2C 0 3748.68 Incorrect Volume D2
3-CY-JFY 3C 43 1698.07 39.49 D2
3-AZ-DJY 3A 78 139.62 1.79 D1
4-DX-SJY 4D 60 1879.8 31.33 D3
4-IY-MJY 4D 80 2828.8 35.36 D3
5-EZ-JFY 5F 91 861.77 9.47 Unknown District
5-JX-DJY 5E 70 1101.1 15.73 D3
Region Region Store-Match Region Lookup Table
North North 1 North Store Lookup District Region
East East 5 East A D1 North
North North 2 North B D1 North
South South 3 South C D2 South
South South 3 South D D3 West
North North 1 North E D3 East
West West 4 West 1 2 3
West West 4 West
Can't finUnknown
Unknown DistrUnknown District
East East 5 East

You might also like