Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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;
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;;
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;