Selecting data from a previous LOAD

[#1]

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

[jonathandienst]

Hi

Use a Resident Load:

MySecondTable:

bla bla bla

RESIDENT MyTable

WHERE bla bla bla;

Hope that helps

Jonathan

[CELAMBARASAN]

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.

[#4]

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....

[jagan]

Hi,

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

Regards,

Jagan.

[CELAMBARASAN]

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?

[#7]

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);

[CELAMBARASAN]

Hi

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

like

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

[#9]

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

[jagan]

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.