topic Re: Loading Different data from multiple folders in QlikView

Loading Different data from multiple folders

qlikview979 — Wed, 25 Nov 2020 16:16:04 GMT

Hi Experts.

I have one folder "DATA" in this folder i have two sub folders like "2016_01_01",2016_01_02", in each sub folder having Same Excel file like "SALES".

in first sub folders Excel fields like

ID,NAME,AGE,SALES

in Second sub folders Excel fields like

ID,NAMES,AGE,SALES

In Two tables one field name is Different.

Here i Want to load Single load statement with two Excels data. With out using( "*" and "&" ) symbols.

is it possible in qlikview?

Regards

Mahesh

Re: Loading Different data from multiple folders

tresB — Fri, 22 Jul 2016 06:28:30 GMT

Check these discussions :

Re: Load multiple xlsx files fromm multiple sub folders‌.

FileName in SubDirectories

Perhaps that would give you an idea.

Re: Loading Different data from multiple folders

qlikview979 — Fri, 22 Jul 2016 06:47:20 GMT

Hi Bro,

please find the attached files.I tired , its reloaded successfully ,but i am getting blank.

please check with my source files.

Regards,

Mahesh

Re: Loading Different data from multiple folders

qlikview979 — Fri, 22 Jul 2016 06:57:01 GMT

Hi Experts,

can any one help me ?

stalwar1‌kush141087‌swuehl‌gwassenaar‌jagan‌

Regards,

Mahesh

Re: Loading Different data from multiple folders

effinty2112 — Fri, 22 Jul 2016 06:59:21 GMT

Hi Mahesh,

You could make a forced concatenation into a temporary table with fields

ID,NAME,NAMES,AGE,SALES

Where NAME is empty NAMES will have a value and vice versa.

Then you can make your table by loading from this resident table and either doing a string concatenation of NAME and NAMES or using a logic like if(IsNull(NAME),NAMES,NAME) as NAME.

Then drop your temporary table.

Cheers

Andrew

Re: Loading Different data from multiple folders

qlikview979 — Fri, 22 Jul 2016 07:02:54 GMT

Hi Bro,

I want two tables data in Single load Statement.

Regards

Re: Loading Different data from multiple folders

effinty2112 — Fri, 22 Jul 2016 07:19:43 GMT

Mahesh,

Try this before you load the excel data:

FieldNameMap:

LOAD * INLINE [

OldName, NewName

NAMES, NAME

];

Rename Fields Using FieldNameMap;

Re: Loading Different data from multiple folders

qlikview979 — Fri, 22 Jul 2016 07:39:12 GMT

Hi Bro,

can you share example with my data. i have attached my files in above.

Regards

Re: Loading Different data from multiple folders

effinty2112 — Fri, 22 Jul 2016 07:45:53 GMT

HI Mahesh,

Regret I'm getting errors trying to unpack your rar file:

1 LOADING DIFFERENT DATA FROM MULTIPLE SUB FOLDERS.rar: File "LOADING DIFFERENT DATA FROM MULTIPLE SUB FOLDERS\2016_01_01\SALES.xlsx" header broken

2 LOADING DIFFERENT DATA FROM MULTIPLE SUB FOLDERS.rar: Unknown method in LOADING DIFFERENT DATA FROM MULTIPLE SUB FOLDERS\2016_01_01\SALES.xlsx

3 LOADING DIFFERENT DATA FROM MULTIPLE SUB FOLDERS.rar: File "LOADING DIFFERENT DATA FROM MULTIPLE SUB FOLDERS\2016_01_02\SALES.xlsx" header broken

4 LOADING DIFFERENT DATA FROM MULTIPLE SUB FOLDERS.rar: Unknown method in LOADING DIFFERENT DATA FROM MULTIPLE SUB FOLDERS\2016_01_02\SALES.xlsx

5 LOADING DIFFERENT DATA FROM MULTIPLE SUB FOLDERS.rar: File "LOADING DIFFERENT DATA FROM MULTIPLE SUB FOLDERS\2016_01_01" header broken

6 LOADING DIFFERENT DATA FROM MULTIPLE SUB FOLDERS.rar: File "LOADING DIFFERENT DATA FROM MULTIPLE SUB FOLDERS\2016_01_02" header broken

7 LOADING DIFFERENT DATA FROM MULTIPLE SUB FOLDERS.rar: File "LOADING DIFFERENT DATA FROM MULTIPLE SUB FOLDERS" header broken

Re: Loading Different data from multiple folders

qlikview979 — Fri, 22 Jul 2016 07:50:14 GMT

Hi Bro,

Please check now

Re: Loading Different data from multiple folders

effinty2112 — Fri, 22 Jul 2016 08:18:59 GMT

Hi Manesh,

Things didn't work out exactly as I'd thought but this script works and I hope it's useful:

Sales:

LOAD * FROM

[LOADING DIFFERENT DATA FROM MULTIPLE SUB FOLDERS\2016_01_02\*.xlsx]

(ooxml, embedded labels, table is Sheet1);

RENAME Field NAMES to NAME;

LOAD * FROM

[LOADING DIFFERENT DATA FROM MULTIPLE SUB FOLDERS\2016_01_01\*.xlsx]

(ooxml, embedded labels, table is Sheet1);

cheers

Andrew

Re: Loading Different data from multiple folders

qlikview979 — Fri, 22 Jul 2016 08:32:05 GMT

Hi Bro,

Thanks,

But not like that i need to load single load statement with out "* and &" symbol

I think this is related script path

please check this

Apply Map in Single line Statement

Re: Loading Different data from multiple folders

swuehl — Fri, 22 Jul 2016 09:08:50 GMT

Maybe use

ALIAS Name AS NAMES;

Before loading your excel files (using a LOAD * FROM ....;)

Or use a

FOR EACH vFile IN FILELIST(...) ... NEXT vFile

loop and create a variable that hold the correct field name based on path. Use this variable in your LOAD statement:

LOAD $(vField),

ID, AGE, SALES

FROM $(vFile);

Re: Loading Different data from multiple folders

kkkumar82 — Fri, 22 Jul 2016 09:39:52 GMT

Hi Mahesh

Does this satisfy ..

for i=1 to 2

if $(i)=1 then

LOAD ID,

NAME,

AGE,

SALES

FROM

(ooxml, embedded labels, table is Sheet1);

else

LOAD ID,

NAMES as NAME,

AGE,

SALES

FROM

(ooxml, embedded labels, table is Sheet1);

ENDIF

Re: Loading Different data from multiple folders

qlikview979 — Fri, 22 Jul 2016 09:48:03 GMT

Hi Bro,

without "ALIAS name" and "*" , i need to do,

i need total two tables data in single load statement.

Regareds,

Mahesh

Re: Loading Different data from multiple folders

qlikview979 — Fri, 22 Jul 2016 09:48:34 GMT

Hi Bro,

without "ALIAS name" and "*" , i need to do,

i need total two tables data in single load statement. not two load statements

Regareds,

Mahesh

Re: Loading Different data from multiple folders

qlikview979 — Fri, 22 Jul 2016 10:37:48 GMT

Hi Experts,

Can any one help me please.

Regards

Re: Loading Different data from multiple folders

swuehl — Fri, 22 Jul 2016 10:44:42 GMT

Maybe we can help you better if you explain what you want to achieve, i.e. why you can't use the suggestions already provided (in other words: explain the restrictions to a possible solution).

Re: Loading Different data from multiple folders

qlikview979 — Fri, 22 Jul 2016 10:50:05 GMT

Hi Bro.

I have one folder "DATA" in this folder i have two sub folders like "2016_01_01",2016_01_02", in each sub folder having Same Excel file like "SALES".

In first sub folders Excel fields like

ID,NAME,AGE,SALES

in Second sub folders Excel fields like

ID,NAMES,AGE,SALES

In Two tables one field name is Different.

Here i Want to load two tables with Single load statement With out using( "*" and "&" ,"Rename") symbols.

Note:- final thing is i need two tables data in single load statement

Regards

Mahesh

Re: Loading Different data from multiple folders

swuehl — Fri, 22 Jul 2016 11:02:32 GMT

As far as I know, you can't address two tables as input in a single LOAD statement.

You can use a wildcard in the filename (star symbol), but this will translated by QV to repeat the LOAD statement for each filename matching the pattern.

You can also use a FOR loop to iterate over the files, as suggested before.

In both cases, you can write a single LOAD statement in your script, but it will be executed by QV multiple times (depending on number of files matching the search pattern).

Still, writing it only once is helpful to avoid redundancy in your script code.