topic Re: Data Island in QlikView

Data Island

didierodayo — Fri, 17 Feb 2017 05:50:43 GMT

Hello,

I am loading QVDS from the last 3 days using the script below. it is working fine BUT because of the qualify I get a table for each day.

How can I now concaternate the same tables together (e.g. all hardwares together in 1 table and all devices together in 1 table)

QUALIFY*;
For Each vTable in 'Devices', 'Hardware'

For i=1 to 3
Let Source = '$(vL.QVDExtractSourcePath)$(vTable)_' & date(today()-$(i), 'YYYYMMDD') & '.qvd';

$(vTable):

LOAD *,

FileBaseName() as QVDNAME

FROM [$(Source)] (qvd);

Re: Data Island

jonathandienst — Fri, 17 Feb 2017 06:27:50 GMT

Perhaps something like this:

For Each vTable in 'Sales', 'Customers', 'Orders'

Let Source = '$(vL.SourcePath)$(vTable)_' & date(today()-$(i), 'YYYYMMDD') & '.qvd';

For i=1 to 3

$(vTable):

LOAD *,

FileBaseName() as QVDNAME

FROM [$(Source)] (qvd);

Next

Change the first line to match your actual requirements.These values are part of the filename (in Source) and used as the final table names as well. The date will not split out the files.

Re: Data Island

Anil_Babu_Samineni — Fri, 17 Feb 2017 06:31:18 GMT

Instead of that long what was the happening with this below code

Table1:

Load *,

FileBaseName() as QVDNAME

FROM [$(Source)] (qvd);

Final:

Load *

Resident Table1

Where Replace(SubField($(Source),'_', -1),'.qvd',' ') >= Date(Today()-3, 'YYYYMMDD')

Re: Data Island

didierodayo — Fri, 17 Feb 2017 07:33:53 GMT

Hi Jonathan,

all the scripts are working the issue is that am getting a synthetic key because the tables are linking.

I don't want to link the tables . also I want 3days qvd per table.

I tried using Qualify but it returning 1 day per table so for 3 tables am getting 3*3 =9 separate tables. I want 3 separate tables.

Thanks

Re: Data Island

didierodayo — Fri, 17 Feb 2017 07:35:21 GMT

Hi Anil thanks for your suggestion but I can't use this because I have 1000s of QVD . IT will load them into memory first.

Re: Data Island

avinashelite — Fri, 17 Feb 2017 07:57:08 GMT

How is those QVD structure ..do they have same number of columns and names??

could you explain bit more on this so that it will be helpful or share a sample app with 3*3 tables so that we could understand the qvd structure

Re: Data Island

didierodayo — Fri, 17 Feb 2017 08:24:55 GMT

Hi Avinash,

so let's assume the 9 qvds below. the content of test1 for all dates is the samenumber of colums etc.. this applies to test2 and test3.

what I am trying to achieve is to view the last 3 days QVD per table (test1;test2;test3) and I don't want the table to be linked because then I will have synthetic keys I want the tables to load as Island tables Test1 Test2 Test3 containing the last 3 days data.

Re: Data Island

sasikanth — Fri, 17 Feb 2017 08:33:03 GMT

Try this

For i=1 to 2

If $(i)=1 Then

Table1:

Load *,

FileBaseName() as QVDNAME

FROM Table1

Where Replace(SubField($(Source),'_', -1),'.qvd',' ') >= Date(Today()-3, 'YYYYMMDD')

ELSEIF $(i)=2 then

Table2:

Load *,

FileBaseName() as QVDNAME

FROM [$(Source)] (qvd);

ENDIF

NEXT i;

Re: Data Island

didierodayo — Fri, 17 Feb 2017 08:44:13 GMT

seems to be missing something.

Re: Data Island

avinashelite — Fri, 17 Feb 2017 10:12:08 GMT

Try this

For Each vTable in 'Sales', 'Customers', 'Orders'

Let Source = '$(vL.SourcePath)$(vTable)_' & date(today()-$(i), 'YYYYMMDD') & '.qvd';

For i=1 to 3

$(vTable):

LOAD *,

FileBaseName() as QVDNAME

FROM [$(Source)] (qvd);

if i=1 then

Qualify *;

$(vTable)_New:

Resident

$(vTable);

Drop table $(vTable);

end if

Next