SQL statements load script editor(Qlik sense)

[#1]

Hello, Can someone help me out with understanding, how does one mimic a SQL where clause in the QLIK sense load script editor ?

Also, I am trying to use a sql query to pull some data from multiple tables using left joins followed by a where clause to capture dates in a dynamic range of 1 month going back from today. Any help is appreciated.

[OmarBenSalem · Accepted solution]

sthing like this:

load

a,

b,

c;

select a,

b,

c

from souce where a=1;

or

load

a,

b,

c where a=1 ;

select a,

b,

c

from souce ;

As for left join, as you  probably know, Qlik associate table by column name, if we have table 1 with colum A, B and C and table 2 with columns A , Y and X, Qlik will consider column A as a mapping key.

Now suppose we have:

table 1:

A,

B,

C from source1;

table2:

A,

X,

Y from source 2;

We want to left join table 2 to table 1(table 1 is principle table).

Here's how we proceed

table 1:

A,

B,

C from source1;

left join(table1)

table2:

A,

X,

Y from source 2;

the outcome will be table1 containing ALL columns A,B , C and the joined X AND Y from table 2.

Suppose that we want to only keep columns where A=1?

This is how we alter our script:

table 1:

load *

where A=1;

A,

B,

C from source1;

left join(table1)

table2:

A,

X,

Y from source 2;

I hope that was clear enough?

[#3]

Well that was definitely helpful. Thank you.

But to go one step further, how can I write the following statement in qlik ?

SELECT o.*

FROM source.table1  o

where o.date_id >= cast(getdate() - 31 as date)

and o.date_id <= cast(getdate() - 1 as date)

[OmarBenSalem]

You can write it as it is.

The good thing with Qlik is that you can use its functionalities in the load statement, but also, you can use what your were using within the select statement.

In your case you can do as follow:

LOAD *;

SELECT o.*

FROM source.table1  o

where o.date_id >= cast(getdate() - 31 as date)

and o.date_id <= cast(getdate() - 1 as date)

With this, you did the manipulations within the select part, then load * to load all your columns from your source after the made transformations.

[#5]

Omar, thanks a ton. It all worked. I am assuming all SQL queries will work as it is.

I have a another question digressing from the load script editor. It's regarding the regular script editor.

I have a dataset where users are a dimension and the customer they perform a specific task for is another dimension.

eg:

Let's say the table represents one task only;

User   Customer    Counter(Always 1)

A           Cus A.          1

B           Cus A.          1

C           Cus B.          1

C           Cus C.         1

A           Cus D.         1

B           Cus E.         1

I want to create a table such that, upon selection of a specific user, the table displays the customers the user worked on. Along with these it also shows the other users that touched these customers and other unique customers that were touched by these new users.

For example for I were to select User A from above:

Customer/User     A     B

Cus A.                 1      1

Cus D.                 1      0

Cus E.                 0      1

I hope I was clear enough in trying to explain what I'm looking for. Let me know if you have any suggestions or if I can help explain the above better. Thanks.

[OmarBenSalem]

You simply use a dynamic table:

As dimension :

Customer

As measure: sum(counter)

then add a column : User:

[#7]

Right, but I do not want user C to be displayed as C and A have no customers in common.

B shows up along with the customers that B touched apart from the ones common to A & B.

[OmarBenSalem]

Hi again Gautam,

Create the same table

dimension: Customer

Column: User:

Measure: =sum(distinct {<User =P({<Customer={"=Count(Distinct User)>1"}>} User ) >}  Counter)

Result:

Hi stalwar1‌, See, when I use the above expression, and click on a user, all the table will turn to null values?

How can I counter this and make it independant of any selection?

[sunny_talwar]

May be this

=Sum(distinct {<User =P({<Customer={"=Count({<User>}Distinct User)=2"}>} User ) >}  Counter)

or

=Sum(distinct {<User =P({<Customer={"=Count({1}Distinct User)=2"}>} User ) >}  Counter)

[OmarBenSalem]

Thank you for the quick answer Sunny but that didn't seem to correct it.

I already tried that:

=Sum(distinct {<User =P({<Customer={"=Count({1}Distinct User)=2"}>} User ) >}  Counter)

I aslo tried :

=Sum(distinct {1<User =P({<Customer={"=Count({1}Distinct User)=2"}>} User ) >}  Counter)

But the result is when I select a User, the table will show only the customers of that specific user..