topic Re: Loading excel: multiple tables in one sheet + data cleaning in App Development

Loading excel: multiple tables in one sheet + data cleaning

nezuko_kamado — Mon, 04 Apr 2022 23:48:08 GMT

The excel sheet has two major tables that separated after "Expired Budget"

How can I create two tables from the original page with adding 'Total' and 'Expiration' columns to the new tables?

Is it possible to separate tables and clean the data at Load editor? Then can you help me on script?

Or only possible at Data manager? If so, how can I do it?

Re: Loading excel: multiple tables in one sheet + data cleaning

Shalom — Tue, 12 Apr 2022 04:48:26 GMT

Hello,

You can use RecNo() in where clause after Load statement to select the rows you need. Please find the attached qvw file.

LOAD ID,
[Charge Object],
Date,
[Expiration date],
Budget,
Title,
Balance,
Total
FROM
[C:\Users\admin\Downloads\Sample.xlsx]
(ooxml, embedded labels, table is Sheet1)
WHERE RecNo() > 0 AND RecNo() < 6;;

Re: Loading excel: multiple tables in one sheet + data cleaning

ramchalla — Tue, 12 Apr 2022 11:28:49 GMT

Hi Nezuko,

As suggested by Shalom, you can use RecNo() function to load the data as individual tables.

please use the below script.

1st Table:

Budget:

Load *
where not WildMatch(ID, '*Total');

LOAD
If(IsNull(ID), Peek(ID,-1), ID) as ID,
"Charge Object",
"Date",
"Expiration date",
Budget,
Title,
Balance

FROM [lib://DataFiles/Sample.xlsx]
(ooxml, embedded labels, table is Sheet1)

Where RecNo() <= 7
;

Left Join (Budget)

Load ID,
Sum(Balance) as Total
Resident Budget
Group BY ID;

2nd Table:

ExpiredBudget:

Load *
where not WildMatch(ID, '*Total');

LOAD
If(IsNull(ID), Peek(ID,-1), ID) as ID,
// ID,
"Charge Object",
"Date",
"Expiration date",
Budget,
Title,
Balance

FROM [lib://DataFiles/Sample.xlsx]
(ooxml, embedded labels, table is Sheet1)

Where RecNo() >= 11
;

Left Join (ExpiredBudget)

Load ID,
Sum(Balance) as Total
Resident ExpiredBudget
Group BY ID;