Resident table syntax when loading from excel files

[isciberras]

Hi Everyone,

I'm having trouble using resident tables to reference a field from a previously loaded table.

Basically, from one tab I'm loading in the following data:

Table1:

Load Order_Ref,

          InvoiceDate

From

From another tab I'm loading:

Left keep

LOAD [InvoiceDate] Resident Table1

Load Order_Ref,

          PurchaseOrderDate,

          InvoiceDate - PurchaseOrderDate as [dif]

From

The issue I'm facing is that I get an error saying it cant find InvoiceDate which would have been loaded from a previous table. Any ideas on what I could do to get this to work?

Thanks,

Isaac

[sunny_talwar]

May be try to add NoConcatenate to Table1 load

Table1:

NoConcatenate

Load Order_Ref,

          InvoiceDate

From

[isciberras]

Thanks but didn't work unfortunately... I keep getting an error message saying it can't find the field previously loaded in

[sunny_talwar]

Can you share your complete script until the error message point?

[Or]

Hit "OK" when the error pops up (and for any additional errors), then check the data model (CTRL+T) to see what exactly did get loaded. That should point you in the direction of a fix.

[isciberras]

Thanks Sunny,

This is my exact script in the first tab:

SET ThousandSep=',';
SET DecimalSep='.';
SET MoneyThousandSep=',';
SET MoneyDecimalSep='.';
SET MoneyFormat='£#,##0.00;-£#,##0.00';
SET TimeFormat='hh:mm:ss';
SET DateFormat='DD/MM/YYYY';
SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff]';
SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';



MainHeader:
NoConcatenate
LOAD ORDR_OWNER,
    ITEM,
    GL_MU,
    GL_ACCT,
    QTY_INVCD,
    VAL_EST,
    SITE,
    DESCR,
    date(CRTE_DATE, 'DD/MM/YYYY') as [PO raised],
    YEAR([CRTE_DATE]) AS YEAR,
    month([CRTE_DATE]) AS MONTH,
    NAME,
    ORDR_REF,
    floor((RowNo()-1/7)+1) as weekCounter,
    Month([CRTE_DATE]) as M1
   
FROM

(ooxml, embedded labels, table is Sheet1);

_____

This is my exact script in my other tab:

left keep
LOAD [PO raised] Resident MainHeader;

LOAD ORDR_REF,
   date(DMYY_RECV, 'DD/MM/YYYY') as [Invoiced],
    date(DMYY_RECV, 'DD/MM/YYYY') - [PO raised] as [dif],
      Month([DMYY_RECV]) as M2,
      VAL_ON_ORDR,
      ITEM


FROM

(ooxml, embedded labels, table is Sheet1);

____

This is the error message:

Field not found - <PO raised>

LOAD ORDR_REF,

   date(DMYY_RECV, 'DD/MM/YYYY') as [Invoiced],

    date(DMYY_RECV, 'DD/MM/YYYY') - [PO raised] as [dif],

      Month([DMYY_RECV]) as M2,

      VAL_ON_ORDR,

      ITEM

[isciberras]

Hi Shoham,

its failing on the field I'm trying to call from the previously loaded table. I think the resident table function is not working/ I didn't code it correctly but don't know how to correct this

[Or]

Based on that, the issue isn't with the left keep - your syntax just isn't doing what you think.

You have a table that holds nothing but the [PO Raised] field.

You are trying to use that table in a load from an Excel file that presumably doesn't have a [PO Raised] field in it.

There's also no connection in the script between [PO Raised] and the last Excel load, so even if you did try to merge them, you wouldn't get anything useful.

You'd probably find it easiest to use a mapping load here - pardon if my syntax isn't perfect, I'm free-typing here.

MapLoad:

Mapping Load ORDR_REF, [PO raised] Resident MainHeader;

LOAD ORDR_REF,
   date(DMYY_RECV, 'DD/MM/YYYY') as [Invoiced],
    date(DMYY_RECV, 'DD/MM/YYYY') - ApplyMap('MapLoad',ORDR_REF) as [dif],
      Month([DMYY_RECV]) as M2,
      VAL_ON_ORDR,
      ITEM


FROM

(ooxml, embedded labels, table is Sheet1);

[isciberras]

Thank you very much for your suggestion, unfortunately I tried it and it didn't work. Thanks just the same though

[sunny_talwar · Accepted solution]

How about this

MainHeader:
NoConcatenate
LOAD ORDR_OWNER,
    ITEM,
    GL_MU,
    GL_ACCT,
    QTY_INVCD,
    VAL_EST,
    SITE,
    DESCR,
    date(CRTE_DATE, 'DD/MM/YYYY') as [PO raised],
    YEAR([CRTE_DATE]) AS YEAR,
    month([CRTE_DATE]) AS MONTH,
    NAME,
    ORDR_REF,
    floor((RowNo()-1/7)+1) as weekCounter,
    Month([CRTE_DATE]) as M1
FROM

(ooxml, embedded labels, table is Sheet1);

_____

This is my exact script in my other tab:

TmpTable:
LOAD ORDR_REF,
    date(DMYY_RECV, 'DD/MM/YYYY') as [Invoiced],
    Month([DMYY_RECV]) as M2,
    VAL_ON_ORDR,
    ITEM
FROM

(ooxml, embedded labels, table is Sheet1);

Left Join

LOAD ORDR_REF,

    ITEM,

    [PO raised]

Resident MainHeader;

FinalTable:

LOAD *,

    [Invoiced] - [PO raised] as [dif]

Resident TmpTable;

DROP Table TmpTable;