Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Don't miss the upcoming Q&A with Qlik session on Qlik Application Automation on November 16th! REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
theaokii
Contributor III
Contributor III

Slipt excel data to tables in data loader

Hi guys,

There is question about the data load

I got 1 excel file that contains tables in one sheet like

TableA 
IDNum
a1
b2
c3
TableB 
IDPnum
a12
b13
c14
TableC 
IDNum
d4
e5
f6

And I want to load it into 2 table seperately as:

[tableA1]

...

[tableB1]

...

Table A will concatenate to table C cause of the similar

The result likes:

TableA1 
IDNum
a1
b2
c3
d4
e5
f6

 

TableB1 
IDPnum
a12
b13
c14

Is it possible to do?

Every help would be appreciated

Thank u

1 Solution

Accepted Solutions
JordyWegman
Partner
Partner

Hi,

Use this:

Table:
LOAD
    A,
    "B",
	IF(SubStringCount(A,'Table')>0,1,0) 			as _indTable,
    Previous(IF(SubStringCount(A,'Table')>0,1,0)) 	as _indHeader,
    RangeSum(IF(SubStringCount(A,'Table')>0,1,0), peek(_indTables)) as _indTables,
    RowNo() as _indRowNo
FROM [lib://Downloads/Test.xlsx]
(ooxml, no labels, table is Sheet1);

For each Table in FieldValueList('_indTables')
	Trace >> $(Table);
    
    NoConcatenate
    $(Table):
    Load
    A as ID_$(Table),
    B as Num_$(Table)
    Resident Table
    Where _indTables = $(Table) and _indTable = 0 and _indHeader = 0
    ;

next

You only have to decide which tables you want to concatenate, but you can do this.

Jordy

Climber

Work smarter, not harder

View solution in original post

4 Replies
jonathandienst
Partner
Partner

Please upload a small sample spreadsheet for more detailed assistance.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
theaokii
Contributor III
Contributor III
Author

It was my mistake. Thank you for attention 😄
JordyWegman
Partner
Partner

Hi,

Use this:

Table:
LOAD
    A,
    "B",
	IF(SubStringCount(A,'Table')>0,1,0) 			as _indTable,
    Previous(IF(SubStringCount(A,'Table')>0,1,0)) 	as _indHeader,
    RangeSum(IF(SubStringCount(A,'Table')>0,1,0), peek(_indTables)) as _indTables,
    RowNo() as _indRowNo
FROM [lib://Downloads/Test.xlsx]
(ooxml, no labels, table is Sheet1);

For each Table in FieldValueList('_indTables')
	Trace >> $(Table);
    
    NoConcatenate
    $(Table):
    Load
    A as ID_$(Table),
    B as Num_$(Table)
    Resident Table
    Where _indTables = $(Table) and _indTable = 0 and _indHeader = 0
    ;

next

You only have to decide which tables you want to concatenate, but you can do this.

Jordy

Climber

Work smarter, not harder

View solution in original post

theaokii
Contributor III
Contributor III
Author

Thank u alot. It works greatly
However, there are 2 points i still consider. Could you enlight me?
1, How to put concatenate for the loop. I have tried but it doesnt work

Spoiler

next

Concatenate
Testconcate:
load ID_$(Table) as ID,
Num_$(Table) as Num
Resident Table
where _indTables = 1 and _indTables = 3
;

 

2, If there are many table names in the sheet which i cant define like Previous(IF(SubStringCount(A,'Table')>0,1,0)), what will i do

I have tried if(not isnull(A) and is null(B)) but idk why it doesnt work

And the concatenate of case 2 if i cant define all the indTables is too complex for me