topic Re: Load value from specific cells in Excel, apply to all data in QlikView

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

Mon, 01 Dec 2014 10:20:24 GMT

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

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

amit_saini — Mon, 01 Dec 2014 10:29:41 GMT

Olle,

Better you define one inline table here.

Thanks,

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

anbu1984 — Mon, 01 Dec 2014 10:34:11 GMT

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;

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

amit_saini — Mon, 01 Dec 2014 10:41:32 GMT

PFA

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

Mon, 01 Dec 2014 12:55:07 GMT

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;

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

Mon, 01 Dec 2014 13:00:23 GMT

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

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

anbu1984 — Mon, 01 Dec 2014 13:02:07 GMT

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

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

Mon, 01 Dec 2014 13:10:21 GMT

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

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

anbu1984 — Mon, 01 Dec 2014 13:27:57 GMT

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

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

its_anandrjs — Mon, 01 Dec 2014 13:47:03 GMT

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

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

Mon, 01 Dec 2014 13:59:04 GMT

Hi Andu,

when testing your script alone, the LOAD crashes "Execution of script failed".

Any idea why? I appreciate your help!

Regards,

Olle

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

Mon, 01 Dec 2014 14:04:32 GMT

Thanks for your contrubution Anand, however as I pointed out above (in anwer to Andu).

I need the Tab1 to be a concatenate load, which does not work with your approach. And neither can I load this first and the other tables after, since the other loads are similar (concatenate and joins etc).

Any I deas how to work around this?

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

anbu1984 — Mon, 01 Dec 2014 14:10:33 GMT

No idea. Check this app