LOADING SplitWise Files with CrossTAB Loading Isse

[lakshmanvepuri]

Hi,

I have an excel sheet with below information with 20000+ rows  ( I have taken sample for 60+ rows ) 

Country

Customer Name

PLAN

DEVICE

2020 Q1 INFLOW

2019 Q1 INFLOW

2019 Q2 INFLOW

2019 Q3 INFLOW

2019 Q4 INFLOW

Splitting the file with every 15 records as COUNTRY_IN_0,  COUNTRY_IN_1, .....

I am trying to load the split files as Crosstab individually and not able to compile it.

enclosing the EXCEL Source file and QVW file . Please help on this.

I want to use the Split files and process with Crosstab and generate Final data files as input for other sources.

Thanks

Vepuri 

[raji6763 · Accepted solution]

hi Lakshman,

try this this script that 

• Split the rows
• generate a separate files with 15 rows.
• use you excel file and you customize your  rows  by changing the value

Your_excel_file:

LOAD RowNo() as row,

"Region Name",
ID,
Productname,
SalesAmount,
"Modified Date"
FROM [lib://DataFiles/sales_full load.xlsx]
(ooxml, embedded labels, table is sales);

LET file=0;
LET count=NoOfRows('Your_excel_file');

DO WHILE count > 0

noconcatenate

Tab:

FIRST (15)

LOAD
"Region Name",
ID,
Productname,
SalesAmount,
"Modified Date"

resident Your_excel_file

where row > $(file)*15 and row < ($(file)+1)*15;

store Tab into [lib://DataFiles/COUNTRY_IN_$(file).txt](txt);

drop Table Tab;

LET count = $(count) - 15;

LET file = $(file) + 1;

LOOP;

regards,

raji