topic Re: Quick Answers! in QlikView

Quick Answers!

Wed, 29 Jan 2014 07:50:49 GMT

Hello to all community!

My questions are quite easy but it is quite important for me and for newbee guys...So your replies quite useful for all starter users..

My problems are adding condition while loading data from source...I dont wanna go to database to add condition and limit the size of data...I need to learn 2 issues which are quite important when we make reports..

First one;

I would like to use only column A and I need only X and Y from column while loading data..How can I write a sciprt when I reload it whole data and select those conditions? (I have tried where condition but dont know why it doesnt fix it up )

Second one:

How can I select date range from DATE column while loading data?

For example I would like to see only date from 01-01-2014 and 05-01-2014?

I have uploaded a test qwv and if you reply me in a short time I really would be appreciated in you..

Thanks for replying me...

Re: Quick Answers!

Wed, 29 Jan 2014 08:00:01 GMT

I need to change the columns like which are uploaded a sample table.I need to select X from current COLUMN A while reloading data and I need to select date range such as from 01/01/2014-03/01/2014..

COLUMN A	COLUMN B	DATE
X,Y,Z	5	1/1/2014
Y,Z,X	10	2/1/2014
Z,X,Y	15	3/1/2014
A	20	4/1/2014
B	25	5/1/2014
C	30	6/1/2014
M	35	7/1/2014
N	40	8/1/2014
K	45	9/1/2014

Re: Quick Answers!

giakoum — Wed, 29 Jan 2014 08:00:42 GMT

data:

LOAD

Where

[COLUMN A]='X'

and [DATE] >= '3/1/2014'

and [DATE] <= '7/1/2014';

LOAD [COLUMN A],

[COLUMN B],

DATE

FROM

(ooxml, embedded labels, table is Sheet1);

This is a load over a load, as you cannot directly place where condition.

Re: Quick Answers!

Sokkorn — Wed, 29 Jan 2014 08:02:08 GMT

Hi,

For question number one:

LOAD [COLUMN A],

[COLUMN B],

DATE

FROM

(ooxml, embedded labels, table is Sheet1) Where WildMatch([COLUMN A],'*X*','*Y*');

For number two:

LOAD [COLUMN A],

[COLUMN B],

DATE

FROM

(ooxml, embedded labels, table is Sheet1)

Where DATE >= Date(Date#('01-01-2014','dd-MM-yyyy')) And DATE <= Date(Date#('05-01-2014','dd-MM-yyyy'));

Is it possible to upload your excel file?

Regards,

Sokkorn

Re: Quick Answers!

alexandros17 — Wed, 29 Jan 2014 08:05:34 GMT

There are several ways in which you can avoid loadind of columns if you have for example

Select col1, col2, col3, ... , coln from ... and you need only col1 and col2 then write Select col1, col2 from ...

in this way dring the loading you can access all other columns in where conditions so you may write

Select col1, col2 from ... where (myDate >= '2014/01/15' and myDate <= '2014/01/20')

You have to pay attention to date format (depending on the format you use)

Hope it helps

Re: Quick Answers!

Anonymous — Wed, 29 Jan 2014 08:07:23 GMT

Load

FROM

WHERE [COLUMN A] = 'X' or [COLUMN A] ='Y' and DATE >= '01/01/2014' and DATE <= '05/01/2014';

Re: Quick Answers!

Wed, 29 Jan 2014 08:09:26 GMT

Thanks Sokkorn for replying me...It has worked finally...you are big man! lol

Re: Quick Answers!

Wed, 29 Jan 2014 08:10:17 GMT

thanks alessandro...your script is working too!

Re: Quick Answers!

Wed, 29 Jan 2014 08:12:30 GMT

Thanks all guys! It is quite important knowledge for freshers and I know that many guys will check out your replies....thanks for nice collaboration!

Re: Quick Answers!

Wed, 29 Jan 2014 09:17:52 GMT

hi Sokkorn,

I have applied to my report and I havent got the result...where is the mistake if my data and sciprt like below;

LOAD [COLUMN A],

[COLUMN B],

DATE

FROM

(ooxml, embedded labels, table is Sheet1)

Where Match([COLUMN A],'Y');

and my data ;

COLUMN A	COLUMN B	DATE
X,Y	5	1/1/2014
Y,Z	10	2/1/2014
Z,A	15	3/1/2014
A,Y	20	4/1/2014
B,Y	25	5/1/2014
C,X	30	6/1/2014
M,Y	35	7/1/2014
N,Y	40	8/1/2014
K,Y	45	9/1/2014

Re: Quick Answers!

Wed, 29 Jan 2014 09:19:16 GMT

Hi Alessandro;

what about if the data is like below and If I want to pick only Y from Column A;

COLUMN A	COLUMN B	DATE
X,Y	5	1/1/2014
Y,Z	10	2/1/2014
Z,A	15	3/1/2014
A,Y	20	4/1/2014
B,Y	25	5/1/2014
C,X	30	6/1/2014
M,Y	35	7/1/2014
N,Y	40	8/1/2014
K,Y	45	9/1/2014

Re: Quick Answers!

Sokkorn — Wed, 29 Jan 2014 09:20:38 GMT

Hi ATACAN,

Let try WildMatch() not Match()

LOAD [COLUMN A],

[COLUMN B],

DATE

FROM

(ooxml, embedded labels, table is Sheet1)

Where WildMatch([COLUMN A],'*Y*');

Regards,

Sokkorn

Re: Quick Answers!

Wed, 29 Jan 2014 09:24:39 GMT

Yep! Now you got the result what I want....thnx a lot man!

Re: Quick Answers!

Sokkorn — Wed, 29 Jan 2014 09:25:57 GMT

Welcome ATACAN