Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Leverage your QlikView investment to modernize BI – see how! Join Group
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