Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

wromanek
New Contributor II

Is it possible to use table name in expression?

Is there any way to use table name in expression if I would like to get data only from one table in case when both tables looks the same?

TAB_1:

loginabstract value
user01x
user02x
user03x

TAB_2:

loginabstract value
user01x
user02x
user04x
user05x

In QlikSense I would like to display a table which would containt login names only from TAB_1. How could I achieve it? If I achieve it - is it possible to build table name in expression dynamically?

1 Solution

Accepted Solutions
Quy_Nguyen
Contributor III

Re: Is it possible to use table name in expression?

Hi,

In this case, you may need add a TableName field in each table, like:

TAB_1

loginTableNameAbstract Value
user01Tab_1xx
user02Tab_1xx
user03Tab_1xx

TAB_2

loginTableNameAbstract Value
user01Tab_2xx
user03Tab_2xx
user05Tab_2xx

Then you can create a filter from TableName field, or use it in set analysis expression.

11 Replies
datanibbler
Esteemed Contributor

Re: Is it possible to use table name in expression?

Hi,

well, that's actually two challenges: You need to find out whether Tab_1 and Tab_2 are identical and then you want to use the tablename in an expression?

There's no easy way to achieve the first - you'd need a key consisting of all fields of the tables and then check whether it exists in the other.

A solution would be (for example) to map that key (made from the fields of the one table) into the other by

- mapping_loading that key and a nr. "1" (hard-coded) RESIDENT from the one table and

- drawing it into the other by using Applymap() in a RESIDENT LOAD from the other table (using the same concatenation of all fields as key for the mapping and 0 as an alternative value).

The table name is not stored anywhere by default afaIk, you have to put it into a variable manually which you can of course make dependent of the outcome of this test.

HTH

Best regards,

DataNibbler

wromanek
New Contributor II

Re: Is it possible to use table name in expression?

In this case the idea is to have lots of identical tables. But they would contain different data. The name of the table would be for instance TAB_2018_06_27, TAB_2018_06_28 so I could use date expression to get a proper table. But I have no idea how to specify that I need all logins from TAB_2018_06_27 not from any other tables which contains same column named 'login'.

Quy_Nguyen
Contributor III

Re: Is it possible to use table name in expression?

Hi,

In this case, you may need add a TableName field in each table, like:

TAB_1

loginTableNameAbstract Value
user01Tab_1xx
user02Tab_1xx
user03Tab_1xx

TAB_2

loginTableNameAbstract Value
user01Tab_2xx
user03Tab_2xx
user05Tab_2xx

Then you can create a filter from TableName field, or use it in set analysis expression.

wromanek
New Contributor II

Re: Is it possible to use table name in expression?

Adding additional column might work but there is actually one more issue which I don't know how to solve. I have a set of data exposed via REST API. Each time I request this API I get same data. I have scheduled a reload task which reloads the app each 10 minutes. What I was expecting was that after each reload QlikSense will append me additional set of data... But some how QlikSense is smart enough to find out that this data is same and it overrides the data. My idea to solve it was adding additional field in to my JSON record which was a random generated GUID. I just thought that if I will have randomly generated guid it will start to treat each record as a new one. But again... instead of appending the data it is simply updating guids with newly generated. So just to demonstrate what I want to achieve:

RELOAD_1 data source:

guiddocument namelogin
guid1Docu1User1
guid2Docu2User2
guid3Docu3User3

RELOAD_2 data source:

guiddocument namelogin
guid4Docu1User1
guid5Docu2User2
guid6Docu3User3

Result I need is:

guiddocument namelogin
guid1Docu1User1
guid2Docu2User2
guid3Docu3User3
guid4Docu1User1
guid5Docu2User2
guid6Docu3User3

But the result I get is:

guiddocument namelogin
guid4Docu1User1
guid5Docu2User2
guid6Docu3User3

So the question right now is: How can I force QlikSense to treat each data reload as a new bunch of data which should be simply appended to existing tables?

Quy_Nguyen
Contributor III

Re: Is it possible to use table name in expression?

You need to store your reload 1 data into a QVD file first. Then every time from the 2nd reload, just append new data to old data.

Here the simple logic

//1.Load data from REST connection

Data:

Load xxx From XXX;

//2.Concatenate with old data in QVD file

Load xxx From Data.qvd;

//3.Store data to QVD file

Store Data into Data.qvd;

wromanek
New Contributor II

Re: Is it possible to use table name in expression?

I have tried this solution... but still it updates instead of appending the data

Quy_Nguyen
Contributor III

Re: Is it possible to use table name in expression?

Not sure that you get my point. May i have a look at your script?

wromanek
New Contributor II

Re: Is it possible to use table name in expression?

This is my script:

LIB CONNECT TO [DOC_PERF_TEST];

[root]:
LOAD
     [login],
     [firstName],
     [lastName],
     [actionId],
     [actionState],
     [guid],
    Time(Now()) as timestamp;
SQL SELECT 
     "login",
     "firstName",
     "lastName",
     "actionId",
     "actionState",
     "guid"
FROM JSON (wrap on) "root" QDL;

Let vFileExsist=if(FileSize([lib://QVDs/Performance.qvd])>0,-1,0);
if $(vFileExsist) then
//2.Concatenate with old data in QVD file
  LOAD
    [login],
    [firstName],
    [lastName],
    [actionId],
    [actionState],
    [guid],
    [timestamp]
  From [lib://QVDs/Performance.qvd] (qvd);
end if;
//3.Store data to QVD file
Store root into [lib://QVDs/Performance.qvd] (qvd);
Quy_Nguyen
Contributor III

Re: Is it possible to use table name in expression?

You missed the astrosphere in FileSize function. So it always return 0. Try this:

Let vFileExsist=if(FileSize('[lib://QVDs/Performance.qvd]')>0,-1,0); 
Community Browser