topic Re: Working of first and Concatenate in QlikView

Working of first and Concatenate

Anonymous — Wed, 04 Jun 2014 07:59:42 GMT

Hello All,

I have the following script:

test:

First 2

LOAD id ,

name,

class ,

num

FROM

(qvd);

Concatenate(test)

LOAD id ,

name,

class,

num

FROM

(qvd);

In above script, function named 'First n' ('n' being any integer value depending on the records available in the table,here 'n' is 2) works fine but if I use the same function after concatenate ,it will not work.

Please help me whether it is possible to use 'First n' after Concatenate or not.

Thanks

Re: Working of first and Concatenate

Wed, 04 Jun 2014 08:04:00 GMT

Hi,

it should work.

This script loads 4 rows for me...Try it:

data_tbl:

LOAD * INLINE [

month, val

1, 1

];

Concatenate (data_tbl)

first 3

LOAD * INLINE [

month, val

16, 1

20, 3

23, 2

24, 4

];

Re: Working of first and Concatenate

tresB — Wed, 04 Jun 2014 08:05:47 GMT

I tried this:

Input:

Load * Inline [

Name

swuehl

Gysbert

Miguel

Jagan

Michael

];

Concatenate

OutPut:

first 2 Load * Inline [

Name

John

manish

Henric

Rob

];

And it worked fine.

Re: Working of first and Concatenate

its_anandrjs — Wed, 04 Jun 2014 08:06:37 GMT

No i believe when you load first 2 records from table 1 but after concatenate all data from the table two concatenate to the table 1, First 2 works for table 1 only and loads only first 2 records.

Eg:-

First 2

test:

LOAD * Inline

[

id,name,class,num

1,a,I,23

2,df,II,34

3,dfs,I,25

4,s,II,25

5,fs,II,67

];

Concatenate(test)

LOAD * Inline

[

id,name,class,num

7,sfs,I,2544

8,fsaafs,II,67555

1,da,I,452

2,das,I,34

4,faf,II,545

];

Re: Working of first and Concatenate

ashfaq_haseeb — Wed, 04 Jun 2014 08:07:31 GMT

It just work fine.

try the below code you will get only 4 rows.

Test:

First 2

load * Inline

[DATE_ID, STORE_ID, POS_NUMBER, TICKET_NUMBER, BARCODE_13, AMOUNT

28/5/2014, 21, 14, 201, 1234567890123, 100

28/5/2014, 21, 14, 301, 2345678901234, 200

28/5/2014, 136, 12, 101, 3456789012345, 150

28/5/2014, 136, 12, 401, 4567890123456, 300

28/5/2014, 101, 10, 501, 1234567890123, 200

28/5/2014, 101, 13, 201, 6789012345678, 150];

Concatenate

First 2

load * Inline

[DATE_ID, STORE_ID, POS_NUMBER, TICKET_NUMBER, BARCODE_13

28/5/2014, 21, 14, 201, 1234567890123

28/5/2014, 21, 14, 301, 2345678901234

28/5/2014, 136, 12, 101, 3456789012345

28/5/2014, 136, 12, 401, 4567890123456

28/5/2014, 103, 15, 501, 9012345678901

28/5/2014, 101, 16, 101, 3456789012345];

Regards

ASHFAQ

Re: Working of first and Concatenate

Anonymous — Wed, 04 Jun 2014 09:58:33 GMT

Hi Dariusz,

Thanks for trying it but if I create qvd of both the tables separately by adding one more field to the second table ,In that case 'First n' will not work .

I tried the following script :-

LOAD

month,

val

FROM

(qvd);

Concatenate (data_tbl)

first 3

LOAD

month,

val

//, data

FROM

(qvd);

Thanks

Re: Working of first and Concatenate

Wed, 04 Jun 2014 10:10:18 GMT

Strange

this works for me....

LOAD month,

val,

FROM

(qvd);

Concatenate

First 2

LOAD month+1 as month,

val+3 as val

FROM

(qvd);

what does it mean: "does not work"? there are more rows loaded or maybe no rows from 2'nd table? or maybe you have some error??

Re: Working of first and Concatenate

Anonymous — Wed, 04 Jun 2014 10:34:12 GMT

Hi,

"Does not work " means all rows of second table are concatenated with the first table .

Re: Working of first and Concatenate

Wed, 04 Jun 2014 10:37:44 GMT

if you delete or comment concatenate statement and load 2'nd table changing field names using as, what do you have loaded into it?

Re: Re: Working of first and Concatenate

Anonymous — Wed, 04 Jun 2014 11:09:15 GMT

Hi Dariusz,

Please find the attachment.

It would be more clear what I am trying to say.

Thanks

Re: Working of first and Concatenate

ashfaq_haseeb — Wed, 04 Jun 2014 11:15:30 GMT

Hi,

How many rows your do you have in you table 2.

Can you post your qvd too.

Regards

ASHFAQ

Re: Re: Working of first and Concatenate

Anonymous — Wed, 04 Jun 2014 11:23:46 GMT

Hi Ashfaq,

Thanks for the concern.

Please find the attachments of qvd.

Regards

Geeta

Re: Re: Re: Working of first and Concatenate

ashfaq_haseeb — Wed, 04 Jun 2014 11:35:44 GMT

Hi,

just try this code.



data_tbl:


LOAD month, 
     val,
     1 as Flag
FROM

(qvd);


concatenate(data_tbl)


first 3


 LOAD month, 
     val, 
     data,
      2 as Flag
FROM

(qvd);

Regards

ASHFAQ

Re: Re: Re: Working of first and Concatenate

Anonymous — Wed, 04 Jun 2014 11:59:43 GMT

Hi Ashfaq,

It works fine .

Thanks a lot

Could you please tell me the use of Flag here?

Regards,

Geeta

Re: Working of first and Concatenate

ashfaq_haseeb — Wed, 04 Jun 2014 12:08:31 GMT

Hi geeta,

By using flags you can differentiate two table I flagged table 1 with 1 and table2 with flag 2.

Now if you select 1 in flag which means this data is coming from table1 and if you select 2 in flag this means data is coming from table2.

If it answered your question, then close the thread by selecting appropriate answer.

Regards

ASHFAQ

Re: Working of first and Concatenate

CELAMBARASAN — Wed, 04 Jun 2014 12:15:12 GMT

Hi Geeta,

I understood your issue.

Seems like a bug when loading in QVD and concatenate it behaves weird.

It loads N+1 records on this combination. If you want to load 3 rows then use 2.

data_tbl:

LOAD month,

val

FROM

[data_tbl.qvd]

(qvd);

Concatenate(data_tbl)

first 2

LOAD month,

val,

data

FROM

[d2.qvd]

(qvd);

or try in this way

data_tbl:

LOAD month,

val

FROM

[data_tbl.qvd]

(qvd);

temp_data_tbl:

first 2

LOAD month,

val,

data

FROM

[d2.qvd]

(qvd);

Concatenate(data_tbl)

LOAD

Resident temp_data_tbl;

DROP Table temp_data_tbl;

Re: Working of first and Concatenate

Anonymous — Wed, 04 Jun 2014 12:46:34 GMT

Hi Celambarasan,

Thanks for the information.

Regards,

Geeta