Hi,
CrossTable:
It helps to convert all DB columns as field values into new field and all DB columns data into another new field based on key column(i.e. first columns in load). This concept is equal to UnPivot function in Sql.
Generic Load:
It is opposite to Crosstable. But it will generate mulitple tables based on key column. This function is equal to Pivot function in Sql.
Eg:
If you have data like this
| Object | Attribute | Value |
| Ball | Color | Red |
| Ball | Diameter | 10 cm |
| Ball | Weight | 100 gms |
| Box | Color | Black |
| Box | Length | 20 cm |
| Box | Width | 10 cm |
| Box | Height | 16 cm |
| Box | Weight | 500 gms |
/*
It helps to convert all DB columns as field values into new field
and all DB columns data into another new fiel based on key column(i.e. first columns in load)
*/
CrossTable(Attribute,Value)
LOAD Object,
Color,
Diameter,
Weight,
Length,
Width,
Height
FROM
ScriptStatementsAndKeyWords.xlsx
(ooxml, embedded labels, table is CrossTable);
/*
It is opposite to Crosstable.
But it will generate mulitple tables based on key column
*/
Generic
LOAD Object,
Attribute,
Value
FROM
ScriptStatementsAndKeyWords.xlsx
(ooxml, embedded labels, table is GenericDB);
Partial Load:
Add: Add is prefix for Load, Select and Map statements. "Add load" statement will execute at normal & partial load. But "Add only load" statement executes only at partial load. It will not check for any duplicates at normal load, but it will checks at partial load
Replace: Replace will replace the previous loaded table and adds the new table with data. It will work at normal and partial load. In normal reload it will load the first table and immediately it will drop the first table, then it will load the replace table along with data. In partial reload it will drop the first table and loads the replace table along with data.
Eg:
If you have data
Persons:
New Persons:
Table1:
LOAD Name,
Number
FROM
ScriptStatementsAndKeyWords.xlsx
(ooxml, embedded labels, table is Persons);
/* This statement works at normal reload and partial reload */
Add
LOAD Name,
Number
FROM
ScriptStatementsAndKeyWords.xlsx
(ooxml, embedded labels, table is NewPersons)
;
// This statement works at partial reload
Add Only
LOAD Name,
Number
FROM
ScriptStatementsAndKeyWords.xlsx
(ooxml, embedded labels, table is NewPersons)
Where not Exists(Name)
;
Try to comment each statement one by one and check with reload and partial reload based on example
