LOADING SplitWise Files with CrossTAB Loading Isse

lakshmanvepuri — Sat, 16 Nov 2024 18:39:00 GMT

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

Re: LOADING SplitWise Files with CrossTAB Loading Isse

raji6763 — Thu, 14 May 2020 11:15:15 GMT

hi Lakshman,

try this this script that

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