topic Re: Resident table syntax when loading from excel files in QlikView

Resident table syntax when loading from excel files

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

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

Re: Resident table syntax when loading from excel files

sunny_talwar — Tue, 17 Oct 2017 13:38:56 GMT

May be try to add NoConcatenate to Table1 load

Table1:

NoConcatenate

Load Order_Ref,

InvoiceDate

From

Re: Resident table syntax when loading from excel files

isciberras — Tue, 17 Oct 2017 14:12:22 GMT

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

Re: Resident table syntax when loading from excel files

sunny_talwar — Tue, 17 Oct 2017 14:18:55 GMT

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

Re: Resident table syntax when loading from excel files

Or — Tue, 17 Oct 2017 14:23:18 GMT

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.

Re: Resident table syntax when loading from excel files

isciberras — Tue, 17 Oct 2017 14:27:34 GMT

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

Re: Resident table syntax when loading from excel files

isciberras — Tue, 17 Oct 2017 14:29:03 GMT

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

Re: Resident table syntax when loading from excel files

Or — Tue, 17 Oct 2017 14:34:37 GMT

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

Re: Resident table syntax when loading from excel files

isciberras — Tue, 17 Oct 2017 14:54:06 GMT

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

Re: Resident table syntax when loading from excel files

sunny_talwar — Tue, 17 Oct 2017 15:04:29 GMT

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;

Re: Resident table syntax when loading from excel files

isciberras — Tue, 17 Oct 2017 15:37:49 GMT

I have been trying to fix this issue for 2 days and this code worked. I can't thank you enough! I appreciate your help very much