Data loading

[abhijith28]

Hi,

I have an issue with the data cleaning

There are two tables.

I have to implement crosstable along with conditional delete of the rows.

And also i have a issue with the date format, while loading the date column is showing as integer values.

Please find the attachment.

[dplr-rn · Accepted solution]

try below script

Temp:
CrossTable(Date,Value,1)
LOAD
    F1 as Name,
    "43466",
    "43497",
    "43525",
    "43556",
    "43586",
    "43617",
    "43647",
    "43678"
FROM [lib://Downloads/New Microsoft Excel Worksheet (3).xlsx]
(ooxml, embedded labels, header is 1 lines, table is Sheet1);

MyTable:
NoConcatenate
load Name
	,Date(Num#(Date), 'MMM-YY') as Date
    ,Value
resident Temp
where not IsNull(Name);

drop table Temp;

output below

Name	Date	Value
A	Jan-19	12.00
A	Feb-19	2.00
A	Mar-19	24.00
A	Apr-19	44.00
A	May-19	2.00
A	Jun-19	43.00
A	Jul-19	8.00
A	Aug-19	67.00
B	Jan-19	13.00
B	Feb-19	5.00
B	Mar-19	66.00
B	Apr-19	66.00
B	May-19	78.00
B	Jun-19	88.00
B	Jul-19	55.00
B	Aug-19	67.00
C	Jan-19	56.00
C	Feb-19	677.00
C	Mar-19	777.00
C	Apr-19	75.00
C	May-19	46.00
C	Jun-19	45.00
C	Jul-19	34.00
C	Aug-19	334.00
x	Jan-19	4.00
x	Feb-19	2.00
x	Mar-19	24.00
x	Apr-19	44.00
x	May-19	2.00
x	Jun-19	43.00
x	Jul-19	8.00
x	Aug-19	23.00
y	Jan-19	5.00
y	Feb-19	5.00
y	Mar-19	66.00
y	Apr-19	66.00
y	May-19	78.00
y	Jun-19	88.00
y	Jul-19	55.00
y	Aug-19	566.00
z	Jan-19	56.00
z	Feb-19	6.00
z	Mar-19	7.00
z	Apr-19	8.00
z	May-19	46.00
z	Jun-19	45.00
z	Jul-19	34.00
z	Aug-19	334.00

[abhijith28]

Thank you for your response