Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Qlik Analytics Tour 2020 Online. Begins August 10th. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator
Creator

LOADING SplitWise Files with CrossTAB Loading Isse

Hi,

 

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

CountryCustomer NamePLANDEVICE 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 

1 Solution

Accepted Solutions
Highlighted
Creator II
Creator II

Re: LOADING SplitWise Files with CrossTAB Loading Isse

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

View solution in original post

1 Reply
Highlighted
Creator II
Creator II

Re: LOADING SplitWise Files with CrossTAB Loading Isse

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

View solution in original post