topic Re: Selecting data from a previous LOAD in QlikView

Selecting data from a previous LOAD

Mon, 19 Nov 2012 14:32:56 GMT

Hi,

Not sure if I can do this.. but lets say in my script I have:

MyTable:

LOAD Column1,

Column2,

Column3

FROM MySpreadsheet.xlsx

(ooxml, embedded labels, table is Sheet1)

But within this LOAD I have elements I wish to load into other parts of my Qlikview report, but rather than having to do another LOAD with a WHERE clause to select only some of the data from the same sheet, can I select from MyTable?

So MyTable:

bla bla bla

Then

MySecondTable:

bla bla bla

FROM MyTable

WHERE bla bla bla

Is this possible and can anyone provide some example syntax? Thanks

Re: Selecting data from a previous LOAD

jonathandienst — Mon, 19 Nov 2012 14:34:55 GMT

Use a Resident Load:

MySecondTable:

bla bla bla

RESIDENT MyTable

WHERE bla bla bla;

Hope that helps

Jonathan

Re: Selecting data from a previous LOAD

CELAMBARASAN — Mon, 19 Nov 2012 14:36:36 GMT

Hi,

Ya you can load from already loaded table to another.Resident keyword is used for this

//load all data

Table1:

Load

Fields

From Excel Source;

//Limited data from already loaded table

Table2:

Load

Fields

Resident

Table1 Where Your conditions.

Re: Selecting data from a previous LOAD

Mon, 19 Nov 2012 14:46:02 GMT

Hi, When I do this:

LOAD Column1

Column2

RESIDENT MainLoad

WHERE MATCH(MyDate,$(vTodaysDate), $(vTodaysDateMinus1));

I get the following error:

Field not found - <MyDate>

But MyDate is being pulled in MainLoad....

Re: Selecting data from a previous LOAD

jagan — Mon, 19 Nov 2012 14:53:36 GMT

Hi,

Can you attach entire script, so that it would be easier to find the issue.

Regards,

Jagan.

Re: Selecting data from a previous LOAD

CELAMBARASAN — Mon, 19 Nov 2012 14:54:45 GMT

There is no Error in this script.

I hope MyDate is field is missing in the load. Getting error in loading MainLoad or in the script u posted here?

Can you upload a sample or post your script alone here?

Re: Selecting data from a previous LOAD

Mon, 19 Nov 2012 14:55:24 GMT

Hi, here it is:

SET ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

SET MoneyFormat='$#,##0.00;($#,##0.00)';

SET TimeFormat='h:mm:ss TT';

SET DateFormat='MM/DD/YYYY';

SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

//Qualify everything

QUALIFY *;

//Main Load - Brings in everything

MainLoad:

LOAD MyDate,

...

FROM MTD_GDBR.xlsx

(ooxml, embedded labels, table is MTD_BALSHEET);

//Varibles

//LET vTodaysDate = Today();

//LET vTodaysDateMinus1 = Date(Today() -1);

//SET vTodaysDate='M/D/YYYY';

//SET vTodaysDateMinus1='M/D/YYYY';

Let vTodaysDate = Num(Today());

Let vTodaysDateMinus1 = Num(Today()-1) ;

//SubqueryByDate - Only brings in what is specified by the variables

SubqueryByDate:

LOAD MyDate,

...

RESIDENT MainLoad

//WHERE MATCH(MyDate,$(vTodaysDate), $(vTodaysDateMinus1));

//FROM MTD_GDBR.xlsx

//(ooxml, embedded labels, table is MTD_BALSHEET)

WHERE MyDate = $(vTodaysDate) OR MyDate = $(vTodaysDateMinus1);

Re: Selecting data from a previous LOAD

CELAMBARASAN — Mon, 19 Nov 2012 14:58:21 GMT

You used Qualify *. Then field names Should has prefixes with table name. because qualify assigns tables name as prefix to all the fields.

LOAD MainLoad.MyDate,

MainLoad.DDA_NIB,

MainLoad.MMDA_OtherIB,

MainLoad.[Reg Cash],

MainLoad.SWEEP,

MainLoad.NIB,

MainLoad.IB,

MainLoad.[TD's],

MainLoad.[Total On- Balance Sheet],

MainLoad.MMMF,

MainLoad.CTE,

MainLoad.[Total Off-Balance Sheet],

MainLoad.TOTAL

RESIDENT MainLoad

Re: Selecting data from a previous LOAD

Mon, 19 Nov 2012 14:58:43 GMT

When I use the same FROM/WHERE against the data source, it works fine. Just get the problem against the RESIDENT table...

Re: Selecting data from a previous LOAD

jagan — Mon, 19 Nov 2012 15:00:01 GMT

Hi,

You used Qualify for the tables, so you need to be give MyDate with table name like MTD_GDBR.MyDate.

Hope this helps you.

Regards,

Jagan.

Re: Selecting data from a previous LOAD

Mon, 19 Nov 2012 15:00:06 GMT

Well I prefer to qualify everything... is there a way around this? I am going to have multiple tables and many with the same naming conventions... I know I could use 'AS' for each, but I would prefer to not to...

Re: Selecting data from a previous LOAD

CELAMBARASAN — Mon, 19 Nov 2012 15:01:34 GMT

If you are about to remove the Qualify *;

Then you should rename the Fields

check it by using No Concatenate before load.

Re: Selecting data from a previous LOAD

CELAMBARASAN — Mon, 19 Nov 2012 15:04:16 GMT

You should rename with AS keyword

Try Qualify * ;

After the MainLoad Table.

Re: Selecting data from a previous LOAD

Mon, 19 Nov 2012 15:07:46 GMT

I got Field not found - <MTD_GDBR.MyDate>

I also tried MainLoad.MyDate but got:

Field not found - <MyDate>

Re: Selecting data from a previous LOAD

Mon, 19 Nov 2012 15:29:10 GMT

Well, that worked.. but why? And this means I can't qualify my main load.... which is annoying.

Re: Selecting data from a previous LOAD

CELAMBARASAN — Mon, 19 Nov 2012 17:24:58 GMT

It is not possible drectly. but you can achieve by something like

Temp:

Load From Excel:

Qualify *;

MainTable:

Load

Resident Temp;

SecTable:

Load

Resident Temp Where condition;

After all your process based on the Temp Table drop it finally.

Drop table Temp;

All of your table is now loaded with table prefixes.