check if table exists

[hopkinsc]

Hi all, i can't get something working in my script and was wondering if anyone can help..

I am reading multiple csv files into a table then concatenating an existing QVD to that table and storing back out (incremental load).

i am trying to get the script to check if the table exists before trying to concatenate the old data. if the table doesnt exist then i want to create an inline table for the old data to concatenate to.

I cant seem o get this right.

an example of what my script looks like..

For each File in filelist ('$(vRawDataDir)Sample_Tx_*.csv')

  Data:

  LOAD

       Date,

       ....,

       ....,

       .....

  FROM [$(File)] (txt, codepage is 1252, embedded labels, delimiter is ',', msq);

Next

//Check if table exists, if not then create inline table

if NoOfRows(Data)=null() then

Data:

Load * inline [

Date

]

;

endif

// Load in old data

If QvdCreateTime('$(vDataDir)Data.qvd')>0 Then

      CONCATENATE (Data)

      LOAD DISTINCT *

      FROM [$(vDataDir)Data.qvd] (qvd);

    Endif

Can anyone help please?

[sunny_talwar]

Try like this:

IF NoOfRows('Data') = 0 then

...

ENDIF

IF NoOfRows('Data') > 0 then

ELSE

Data:

Load * inline [

Date

];

ENDIF

[hopkinsc]

Hi, thnaks for your reply.

that doesnt work either, i am getting a table not found error..

i have just tried this to see if the inline table is being created...

IF NoOfRows('Data') = 0 then

Data:

Load * inline [

Date

]

;

endif

exit script;

but its not..

this is the only thing in the script (apart from the variables being created) so there is definitely no tables existing.

[sunny_talwar]

This seems to be working in my attached sample:

LET vVar = NoOfRows('Data');

IF NoOfRows('Data') > 0 then

ELSE

  Data:

  LOAD * Inline [

  Dim1

  a

  ];

ENDIF

[sunny_talwar]

Look at my response below with a sample

[hopkinsc]

thats the wrong way around. i need to create the inline table BEFORE concatenating the old data otherwise there willbe nothing to concatenate to.

so i need the if to be <1 or =null but i cant get that working.

[sunny_talwar]

I am checking if NoOfRows in data table is greater than 0, then do nothing, else create a inline table. Isn't this what you are looking for? This is a indirect method, but seems to be working

[santiago_respane]

Hi!
This is what you need:

IF IsNull(TableNumber('MyTableName')) THEN
TRACE DOESNT EXISTS;
ELSE
TRACE EXISTS;
endif

Let me know if it helps.
Regards,

[maxgro]

For each File in filelist ('$(vRawDataDir)Sample_Tx_*.csv')

  Data:

  LOAD

       Date,

       ....,

       ....,

       .....

  FROM [$(File)] (txt, codepage is 1252, embedded labels, delimiter is ',', msq);

Next;

//Check if table exists, if not then create inline table

if  alt(NoOfRows('Data'),0) =0 then               // EDIT added alt

     Data:

     Load * inline [

     Date

     ]

     ;

endif;

// Load in old data

If QvdCreateTime('$(vDataDir)Data.qvd')>0 Then

      CONCATENATE (Data)

      LOAD DISTINCT *

      FROM [$(vDataDir)Data.qvd] (qvd);

Endif;

[Kushal_Chawda]

try below

For each File in filelist ('$(vRawDataDir)Sample_Tx_*.csv')

  Data:

  LOAD

       Date,

       ....,

       ....,

       .....

  FROM [$(File)] (txt, codepage is 1252, embedded labels, delimiter is ',', msq);

Next

//Check if table exists, if not then create inline table

if isnull(NoOfRows('Data')) then

Data:

Load * inline [

Date

]

;

endif

// Load in old data

If filesize('$(vDataDir)Data.qvd')>0 Then

      CONCATENATE (Data)

      LOAD DISTINCT *

      FROM [$(vDataDir)Data.qvd] (qvd);

    Endif