topic Qualify * in QlikView

Qualify *

Mon, 10 Aug 2009 11:27:23 GMT

Hi Team,

Qualify *; doesn't work if in load script i am taking *.xls in from clause. Is is so or there is some mistake from my side? I am picking up data from two data sources and one field is common. I dont want to link both data sources.

Main:
Load A,
C,
D
From [..\Data\*.xls] (biff, embedded labels, table is Sheet1$);

Qualify *;

Temp:
Load A,
B
From [..\Data\TranData\*.xls] (biff, embedded labels, table is Sheet1$);

Unqualify *;

When i reload the data, qv is creating multiple tables in data cloud like Temp, Temp1, Temp2 for each xl file respectively.

Logically it should not happen as *.xls is just to say qv that instead of one xl, load multiple xl files.
When i renamed fields in my later script, it worked fine. I am not able to understand why it is happening.

Any Suggestion !!

Qualify *

dolphin123 — Mon, 10 Aug 2009 12:19:41 GMT

Hey Arun,

//********************* UR Existing code ******************************//

Main:
Load A,
C,
D
From [..\Data\*.xls] (biff, embedded labels, table is Sheet1$);

Qualify *;

Temp:
Load A,
B
From [..\Data\TranData\*.xls] (biff, embedded labels, table is Sheet1$);

Unqualify *;

//************************************************************************

just change the position of the two qualify and unqualify statements as shown below..

//****************** modified code ********************************

Qualify *;

Unqualify A;

Main:
Load A,
C,
D
From [..\Data\*.xls] (biff, embedded labels, table is Sheet1$);

Temp:
Load A,
B
From [..\Data\TranData\*.xls] (biff, embedded labels, table is Sheet1$);

//*******************************************************************

Qualify *

Mon, 10 Aug 2009 12:27:31 GMT

thanks for the repyl but as i wrote earlier also that i dont want both data sources to be linked

Qualify *

Mon, 10 Aug 2009 16:45:20 GMT

QlikView loads a spreadsheet using the name of the SHEET as the table name, not the name of the FILE, unless you add the table name, followed by a colon, before the load. You have done the latter, so your table will be called Temp. QlikView recognises the QUALIFY request, but you have told it to use the same table name for the load, so it creates Temp, Temp1, Temp2. The filenames are irrelevant in the case you have given; you could capture them in a For each..next loop - see the Help for an example.

Qualify *

Mon, 10 Aug 2009 17:10:19 GMT

Hi,

I tried as mentioned below but nothing is getting loaded...m i missing something

qualify

For

LOAD Fld1,
Fld2
FROM $(fldr)\*.xls (biff, embedded labels, table is Sheet1$);*;

Unqualify

Each fldr in DirList('..\Data\test\*')

Test:

Qualify *

Mon, 10 Aug 2009 17:12:11 GMT

Sorry, pls ignore previous one

qualify *;

For Each fldr in DirList('..\Data\test\*')
Test:
LOAD Fld1,
Fld2
FROM $(fldr)\*.xls (biff, embedded labels, table is Sheet1$);
Next

Unqualify *;

Qualify *

Mon, 10 Aug 2009 17:54:25 GMT

You need the variable to be the table name... try this:

qualify *;

For Each fldr in FileList('..\Data\test\*.xls')
$(fldr):
LOAD Fld1,
Fld2
FROM '..\Data\test\$(fldr)' (biff, embedded labels, table is Sheet1$);

Next fldr

unqualify *;

If you run the script using debug you will see each load statement with the variables translated, which may help you to fix problems.