Load value from specific cells in Excel, apply to all data

[#1]

Hi,

I need to fecth the process date and time showing on top of the excel sheet, and then apply it to the other data.

Pls see example below, sample file attached.

Any ideas?

Regards,

Olle

[amit_saini]

Olle,

Better you define one inline table here.

Thanks,

AS

[anbu1984 · Accepted solution]

Tbl:

LOAD currency,

     [account no.],

     CTPY,

     [value date],

     [CR/DR],

     amount,

     status,

     reference,

     type,

     ProcessDate,

     ProcessTime,

     1 As Key

FROM

[sampleDateTime.xls]

(biff, embedded labels, header is 2 lines, table is dateTime$);

Join(Tbl)

LOAD Date(@1) As Date,

     Time(@2) As Time,

     1 As Key

FROM

[sampleDateTime.xls]

(biff, no labels, header is 1 lines, table is dateTime$) Where RecNo() < 2;

[amit_saini]

PFA

[#5]

Hi Anbu,

works well alone,

however the Tbl needs to be a Concatenate LOAD, and when I apply this as per below, I receive an error message saying that the Join (Tbl) TABLE cannot be found.

Aby ideas?

Regards,

Olle

Tbl:

CONCATENATE LOAD currency,

     [account no.],

     CTPY,

     [value date],

     [CR/DR],

     amount,

     status,

     reference,

     type,

     ProcessDate,

     ProcessTime,

     1 As Key

FROM

[sampleDateTime.xls]

(biff, embedded labels, header is 2 lines, table is dateTime$);

Join(Tbl)

LOAD Date(@1) As Date,

     Time(@2) As Time,

     1 As Key

FROM

[sampleDateTime.xls]

(biff, no labels, header is 1 lines, table is dateTime$) Where RecNo() < 2;

[#6]

Hi Amit,

nice work, however needs to be dynamic. The report data amounts varies from a few rows one day to hundreds of rows another report day.

Best Regards,

Olle

[anbu1984]

You can keep Tbl as first table in your script and then add concatenate to remaining tables.

Tbl:

LOAD currency,

     [account no.],

     CTPY,

     [value date],

     [CR/DR],

     amount,

     status,

     reference,

     type,

     ProcessDate,

     ProcessTime,

     1 As Key

FROM

[sampleDateTime.xls]

(biff, embedded labels, header is 2 lines, table is dateTime$);

Join(Tbl)

LOAD Date(@1) As Date,

     Time(@2) As Time,

     1 As Key

FROM

[sampleDateTime.xls]

(biff, no labels, header is 1 lines, table is dateTime$) Where RecNo() < 2;

CONCATENATE Load * ..

[#8]

Problem is I have s similar load before. With Joins and stuff

[anbu1984]

Dt:

LOAD Date(@1) As Date,

     Time(@2) As Time,

     1 As Key

FROM

[sampleDateTime.xls]

(biff, no labels, header is 1 lines, table is dateTime$) Where RecNo() < 2;

Let vDt=Peek('Date',0,'Dt');

Let vTm=Peek('Time',0,'Dt');

Drop Table Dt;

Concatenate

LOAD currency,

     [account no.],

     CTPY,

     [value date],

     [CR/DR],

     amount,

     status,

     reference,

     type,

     ProcessDate,

     ProcessTime,

     '$(vDt)' As Date,

     '$(vTm)' As Time

FROM

[sampleDateTime.xls]

(biff, embedded labels, header is 2 lines, table is dateTime$);

[its_anandrjs]

Hi Olle,

Try this load script for dynamic loading of the field and load the table without the labels and in the load script give names to the fields. And then join the another table with ProcessDate and ProcessTime only loading and with the key field join this table to get rest of the two fields in the above table

Tab1:

LOAD @1 as currency,

     @2 as [account no.],

     @3 as CTPY,

     @4 as [value date],

     @5 as [CR/DR],

     @6 as amount,

     @7 as status,

     @8 as reference,

     @9 as type,

     1 as Key

FROM

sampleDateTime.xls

(biff, no labels, table is dateTime$) Where IsText(@1) and RecNo() > 3;

Left Join

Tab2:

LOAD Date(@1) as ProcessDate,

          Time(@2,'hh:mm') as ProcessTime,

          1 as Key

FROM

sampleDateTime.xls

(biff, no labels, table is dateTime$) Where not IsText(@1);

And then you get single table as you required

Let me know where any change requires.

Regards

Anand