topic Re: Load Concatenate in QlikView

Load Concatenate

marco_puccetti — Thu, 27 Jun 2013 14:56:26 GMT

Hello i've used this istruction to load some data in qlikview:

COUNTRIES:

LOAD Country,

Capital,

[Area(km.sq)],

Round(Num#(Trim([Population(mio)]))) as Population,

[Pop. Growth],

Currency,

Inflation,

[Official name of Country]

FROM

$(m_oszSourcesFile)Country1.csv

(txt, codepage is 1252, embedded labels, delimiter is ',');

COUNTRIES:

Concatenate(COUNTRIES)

LOAD Country,

Capital,

[Area(km.sq)],

Round(Num#(Trim([Population(mio)]))) as Population,

[Pop. Growth],

Currency,

Inflation,

[Official name of Country]

FROM

$(m_oszSourcesFile)Country2.csv

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

COUNTRIES:

Concatenate(COUNTRIES)

LOAD Country,

Capital,

[Area(km.sq)],

Round(Num#(Trim([Population(mio)]))) as Population,

[Pop. Growth],

Currency,

Inflation,

[Official name of Country]

FROM

$(m_oszSourcesFile)Country3.csv

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

I need to load the data of three different csv in a single table.

The loading process works but there are 2 data duplicated in the table generated and using the count function to get the total number of rows it returns a number that is the effective total of rows plus 5 elements (2 element is ok but the other three?).

I have manually checked the data.

The csv are comma separated data.

Have i made some mistakes in the load script?

Thanks!

Marco

Re: Load Concatenate

swuehl — Thu, 27 Jun 2013 15:16:50 GMT

Marco,

I can't see anything wrong with your code at the moment.

Any chance that you upload your csv files (or part of the files that demonstrate your issue) here to the forum (use advanced editor or edit your first posting to be able to upload files).

Re: Load Concatenate

marco_puccetti — Thu, 27 Jun 2013 15:51:20 GMT

These are the files i have loaded.

Thanks you.

Marco

Re: Load Concatenate

swuehl — Thu, 27 Jun 2013 15:57:34 GMT

Ok, I get 197 rows loaded into my data model.

Which ones are the assumingly duplicated?

Re: Load Concatenate

marco_puccetti — Thu, 27 Jun 2013 16:04:47 GMT

Haiti and Ivory Coast are the two cities duplicated.

So my load procedure is correct and generate one table with all the three csv data?

Marco

Re: Load Concatenate

swuehl — Fri, 28 Jun 2013 09:59:30 GMT

I just can't see any problems.

I've attached the application. Please check.

Re: Load Concatenate

marco_puccetti — Fri, 28 Jun 2013 10:03:41 GMT

I can't understand how can be possible, because i've read that Qlikview's strongness is associative technology.

So duplicate data that are key field should be present once time.

Isn't it?

How can i solve this problem?

Thank you.

Marco

Re: Load Concatenate

swuehl — Fri, 28 Jun 2013 10:07:38 GMT

I can't understand how can be possible, because i've read that Qlikview's strongness is associative technology.
So duplicate data that are key field should be present once time.

Marco,

I am sorry, I just don't get your point.

I don't see any duplicate data, Haiti and Ivory Coast are not duplicated as far as I see (frequency = 1).

All records are loaded into one single table, so there is not key involved. Are you using a more complex data model?

Then you need to post more details.

Re: Load Concatenate

Fri, 28 Jun 2013 10:15:45 GMT

Hi,

Since all your fields are the same, you can load everything in 1 single statement and everything will be concatenated automatically:

COUNTRIES:

LOAD Country,

Capital,

[Area(km.sq)],

Round(Num#(Trim([Population(mio)]))) as Population,

[Pop. Growth],

Currency,

Inflation,

[Official name of Country]

FROM

$(m_oszSourcesFile)Country*.csv

(txt, codepage is 1252, embedded labels, delimiter is ',');

Basically instead of Country1, Country2 and Country3 use "Country*".

Re: Load Concatenate

marco_puccetti — Fri, 28 Jun 2013 10:19:49 GMT

Well, i have simply loaded data in a single table with the attached results.

Marco

Re: Load Concatenate

swuehl — Fri, 28 Jun 2013 10:28:30 GMT

Ok, your script is more complex. You should have posted that information, so we are able to find the cause of your problem.

Country is a key field (at least to your flags table).

You are doing a count(Country), i.e. a count on a key field, which is not allowed. Do a count distinct instead.

Re: Load Concatenate

marco_puccetti — Fri, 28 Jun 2013 10:31:09 GMT

I have used your indication but i have still the same result.

Finally the displayed data are more than the data loaded by the script.

Is it correct?

Marco

Re: Load Concatenate

marco_puccetti — Fri, 28 Jun 2013 10:36:04 GMT

Count distinct returns exactly the same number.

Marco

Re: Load Concatenate

swuehl — Fri, 28 Jun 2013 10:41:13 GMT

Is it possible that you upload a sample qvw that demonstrate your issue?

edit:

And have you double checked that your csv file with flag / Country information does not introduce these additional records?

Re: Load Concatenate

oleg_orlov — Fri, 28 Jun 2013 10:51:12 GMT

Hi!

If you want to be sure that you are loading distinct values you should use function Exists.

For example:

Values:

LOAD Distinct

[Field]

FROM Source1;

Concatenate (Values)

LOAD Distinct

[Field]

FROM Source2

Where not Exists ([Field]);

QlikView Help:

exists(field [ , expression ] )

Determines whether a specific field value exists in a specified field of the data loaded so far. Field is a name or a string expression evaluating to a field name. The field must exist in the data loaded so far by the script. Expr is an expression evaluating to the field value to look for in the specified field. If omitted, the current record’s value in the specified field will be assumed.

Re: Load Concatenate

Fri, 28 Jun 2013 10:51:24 GMT

It seems like your duplicate rows comes from somewhere else that these 3 CSVs... Check wether the tables associated to COUNTRIES don't have these countries in it with a different case.

As swuehl said, it's probably in the Flags table.

Re: Load Concatenate

marco_puccetti — Fri, 28 Jun 2013 14:11:00 GMT

Perhaps the qlikview engine, manipulate the data in memory in some mode, that now i can't understand.

I' ve tried all the methods above mentionned but they don't work.

Can you tell me how many copy of a key are mantained in memory in associative mode?

Thank you

Marco

Re: Load Concatenate

marco_puccetti — Fri, 28 Jun 2013 15:13:55 GMT

I have also checked the flags table but the duplicated data aren't present.

Marco

Re: Load Concatenate

swuehl — Fri, 28 Jun 2013 15:15:49 GMT

It's really hard to help you with the information present.

No chance to upload your sample file or a sample that demonstrate the issue (I think you just need to create your COUNTRIES table and the table(s) linked to Country field)?