Data Rejecting while loading from csv

[mhmmd_srf]

Hi All,

I am loading data from csv file.

One record is rejecting.

If value is 5197102, then only it is rejecting. I have tested with different values, it was not rejecting. Below is the tested value where it is working fine:

1234, 519,  51971,  519710, 000519,  0000519,  3519710,  35197103.

I am editing values manually. It looks very strange. Please suggest.

Regards,

Sarif

[petter]

If you can upload the CSV or parts of the CSV so we could try to reproduce in someway that would be very helpful.

Do you have the part of the load script to show us too?

[mhmmd_srf]

Below is the scrip in qlik:

Directory;

LOAD @1,

     @2,

     @3,

     @4,

     @5,

     @6,

     @7

FROM

[..\..\..\1106_NDC01112017\1106_NDC01112017.csv]

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

[mhmmd_srf]

I am attaching the csv.

the record is:

35197103|28232|066068|AAA|036856|2|BBB

[petter]

I have no problem loading the CSV file into Qlik Sense - I don't get any error messages.

What do you mean by rejection? Do you get any warning or does the reload give error messages?

Is it that you can't find the specific value after loading the CSV?

[mhmmd_srf]

can u please replace 35197103 by 5197102 and reload? This particular record is not coming.

Thanks,

Sarif

[petter]

I have the row containing the 5197102 - but it shows as 0005197102 since that is how it is in the CSV file and that particular column has a mix of numeric and alphanumeric values.

Nothing is rejected at all. However the data and how it is interpreted by Qlik Sense seems to be the issue here.

The first column should it be interpreted as characters/text even if it looks like numbers? This column contains alphanumeric values too like the 3rd to 22nd row/record in the CSV file:

|00000|000000||000000||,,,,,,,,
00000004031066|00000|000000||000000||,,,,,,,,
000019N050D0|00000|000000||000000||,,,,,,,,
000019N068B0|00000|000000||000000||,,,,,,,,
000019N091B0|00000|000000||000000||,,,,,,,,
000019N091D0|00000|000000||000000||,,,,,,,,
000019N094B0|00000|000000||000000||,,,,,,,,
000019N094D0|00000|000000||000000||,,,,,,,,
000019N096B0|00000|000000||000000||,,,,,,,,
000019N12028|00000|000000||000000||,,,,,,,,
000019N12056|00000|000000||000000||,,,,,,,,
000019N12063|00000|000000||000000||,,,,,,,,
000019N12099|00000|000000||000000||,,,,,,,,
000019N132F0|00000|000000||000000||,,,,,,,,
000019N180G0|00000|000000||000000||,,,,,,,,
000019N180M0|00000|000000||000000||,,,,,,,,
000019N450A0|00000|000000||000000||,,,,,,,,
000019N450A1|00000|000000||000000||,,,,,,,,
000019N452A0|00000|000000||000000||,,,,,,,,
000019N452A1|00000|000000||000000||,,,,,,,,
000019N60110|00000|000000||000000||,,,,,,,,
000019N60220|00000|000000||000000||,,,,,,,,
00012546003094|00000|000000||000000||,,,,,,,,
00012546003575|00000|000000||000000||,,,,,,,,

How about leading zeros on values that otherwise looks like numbers? Are they really numbers or should they be treated as strings keeping leading zeros?

Qlik is very flexible it doesn't put any data type constraint on what you can put in a single field. It can be a mix of all the different data types that Qlik supports. That is very different from what normal databases (SQL, relational databases) do - they almost always demand a single data type for a specific column.

If you want to enforce strictly that a field should have just a single data type you have to use the Text() and Num()/Num#(), Date(),Date#() etc category of functions.

If you try this you can explore the difference between @1, @1_Text and @1_Num# and see if that makes more sense:

[petter]

I get exactly the same number of rows into Qlik as the number of rows in the CSV: 317683 # of rows

There is no loss of rows/records....

Actually this screen-shot from a search in a sheet and corresponding load script shows how it works better:

[Digvijay_Singh]

Not sure but in all the list of numbers working fine for you, 5197102 is the only no which is already available in the field as 0005197102( over and above the changed one).

I think since QlikSense stores unique values in the field, when you change 35197103 to 5197102, Qliksense don't store it separately and just refers to the same no which is stored already as 0005197102.