topic Re: check if table exists in QlikView

check if table exists

hopkinsc — Mon, 07 Dec 2015 17:23:12 GMT

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

//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?

Re: check if table exists

sunny_talwar — Mon, 07 Dec 2015 17:28:31 GMT

Try like this:

IF NoOfRows('Data') = 0 then

...

ENDIF

IF NoOfRows('Data') > 0 then

ELSE

Data:

Load * inline [

Date

];

ENDIF

Re: check if table exists

hopkinsc — Mon, 07 Dec 2015 17:34:34 GMT

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.

Re: check if table exists

sunny_talwar — Mon, 07 Dec 2015 17:34:57 GMT

This seems to be working in my attached sample:

LET vVar = NoOfRows('Data');

IF NoOfRows('Data') > 0 then

ELSE

Data:

LOAD * Inline [

Dim1

];

ENDIF

Re: check if table exists

sunny_talwar — Mon, 07 Dec 2015 17:35:29 GMT

Look at my response below with a sample

Re: check if table exists

hopkinsc — Mon, 07 Dec 2015 17:36:39 GMT

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.

Re: check if table exists

sunny_talwar — Mon, 07 Dec 2015 17:38:18 GMT

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

Re: check if table exists

santiago_respane — Mon, 07 Dec 2015 17:42:49 GMT

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,

Re: check if table exists

maxgro — Mon, 07 Dec 2015 18:01:13 GMT

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;

Re: check if table exists

Kushal_Chawda — Mon, 07 Dec 2015 18:04:42 GMT

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

//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

Re: check if table exists

rwunderlich — Mon, 07 Dec 2015 19:00:20 GMT

Since your inline table is empty, why not just create it every time and forget about the conditional test?

-Rob

http://masterssummit.com

http://qlikviewcookbook.com

Re: check if table exists

hopkinsc — Mon, 07 Dec 2015 22:05:18 GMT

thats a good point, thanks Rob!