Data Island

[didierodayo]

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

  Next

Next

[jonathandienst]

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

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.

[Anil_Babu_Samineni]

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

[didierodayo]

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

[didierodayo]

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.

[avinashelite]

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

[didierodayo]

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.

[sasikanth]

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;

[didierodayo]

seems to be missing something.

[avinashelite]

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

Next