topic Re: Same field and value...but two records in QlikView

Same field and value...but two records

Wed, 25 Nov 2020 16:16:04 GMT

I have this file, and the AlphaProdID field is always LocalBoise as you can see.

I run it through this QVW to format it, but I get 2 LocalBoise in the AlphaProd Field

I have attached all necessary sample data.

Even if you just drag it in to Qlikview, then separate it by SOH delimiter, you will see two of the same LocalBoise under AlphaProdID

Please, any help is appreciated.

Re: Same field and value...but two records

sunny_talwar — Fri, 30 Jun 2017 16:57:05 GMT

This seems to have worked

Table:

LOAD *

FROM

[..\..\Downloads\extraboise.dat]

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

Re: Same field and value...but two records

Fri, 30 Jun 2017 17:00:38 GMT

The thing is, I run it through this to get the fields generated correctly.

Can you see anything wrong with how this is setup?

Re: Same field and value...but two records

sunny_talwar — Fri, 30 Jun 2017 17:06:30 GMT

May be try adding Trim

Fact:

LOAD Trim(subfield(@1, chr(3), 1)) as Name1,

Trim(subfield(@1, chr(3), 2)) as Data1,

Trim(subfield(@2, chr(3), 1)) as Name2,

Trim(subfield(@2, chr(3), 2)) as Data2,

Trim(subfield(@3, chr(3), 1)) as Name3,

Trim(subfield(@3, chr(3), 2)) as Data3,

Trim(subfield(@4, chr(3), 1)) as Name4,

Trim(subfield(@4, chr(3), 2)) as Data4,

Trim(subfield(@5, chr(3), 1)) as Name5,

Trim(subfield(@5, chr(3), 2)) as Data5,

Trim(subfield(@6, chr(3), 1)) as Name6,

Trim(subfield(@6, chr(3), 2)) as Data6,

Trim(subfield(@7, chr(3), 1)) as Name7,

Trim(subfield(@7, chr(3), 2)) as Data7,

Trim(subfield(@8, chr(3), 1)) as Name8,

Trim(subfield(@8, chr(3), 2)) as Data8,

Trim(subfield(@9, chr(3), 1)) as Name9,

Trim(subfield(@9, chr(3), 2)) as Data9,

Trim(subfield(@10, chr(3), 1)) as Name10,

Trim(subfield(@10, chr(3), 2)) as Data10,

Trim(subfield(@11, chr(3), 1)) as Name11,

Trim(subfield(@11, chr(3), 2)) as Data11,

Trim(subfield(@12, chr(3), 1)) as Name12,

Trim(subfield(@12, chr(3), 2)) as Data12,

Trim(subfield(@13, chr(3), 1)) as Name13,

Trim(subfield(@13, chr(3), 2)) as Data13,

Trim(subfield(@14, chr(3), 1)) as Name14,

Trim(subfield(@14, chr(3), 2)) as Data14,

Trim(subfield(@15, chr(3), 1)) as Name15,

Trim(subfield(@15, chr(3), 2)) as Data15,

Trim(subfield(@16, chr(3), 1)) as Name16,

Trim(subfield(@16, chr(3), 2)) as Data16,

Trim(subfield(@17, chr(3), 1)) as Name17,

Trim(subfield(@17, chr(3), 2)) as Data17,

Trim(subfield(@18, chr(3), 1)) as Name18,

Trim(subfield(@18, chr(3), 2)) as Data18,

Trim(subfield(@19, chr(3), 1)) as Name19,

Trim(subfield(@19, chr(3), 2)) as Data19,

Trim(subfield(@20, chr(3), 1)) as Name20,

Trim(subfield(@20, chr(3), 2)) as Data20,

Trim(subfield(@21, chr(3), 1)) as Name21,

Trim(subfield(@21, chr(3), 2)) as Data21,

Trim(subfield(@22, chr(3), 1)) as Name22,

Trim(subfield(@22, chr(3), 2)) as Data22,

Trim(subfield(@23, chr(3), 1)) as Name23,

Trim(subfield(@23, chr(3), 2)) as Data23,

Trim(subfield(@24, chr(3), 1)) as Name24,

Trim(subfield(@24, chr(3), 2)) as Data24

FROM

(txt, codepage is 1252, no labels, delimiter is \x1, msq);

Re: Same field and value...but two records

Fri, 30 Jun 2017 17:37:10 GMT

Didn't work

still has two records in AlphaProdID

Re: Same field and value...but two records

sunny_talwar — Fri, 30 Jun 2017 17:55:35 GMT

Don't know... may be use the code I shared because what I tried seems to be working for me

Re: Same field and value...but two records

Fri, 30 Jun 2017 17:59:31 GMT

I tried exactly that and it didn't work for me.

Could you try running this QVW on your end and see if it works for you?

Re: Same field and value...but two records

sunny_talwar — Fri, 30 Jun 2017 18:03:52 GMT

Playing around with me? This is not what I gave... my code is this

Table:

LOAD @1,

@2,

@3,

@4,

@5,

@6,

@7,

@8,

@9,

@10,

@11,

@12,

@13,

@14,

@15,

@16,

@17,

@18,

@19,

@20,

@21,

@22,

@23,

@24,

@25,

@26,

@27,

@28,

@29,

@30,

@31,

@32,

@33,

@34,

@35,

@36,

@37,

@38,

@39,

@40,

@41,

@42,

@43,

@44,

@45,

@46

FROM

[..\..\Downloads\extraboise.dat]

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

Your delimiter is \x1 and mine is spaces. Did you even try to open my app? You didn't, right?

Re: Same field and value...but two records

sunny_talwar — Fri, 30 Jun 2017 18:06:36 GMT

Oh and I also did not use SubField function here

Re: Same field and value...but two records

Fri, 30 Jun 2017 18:16:25 GMT

Okay I see that your version is much simpler than my version.

Thank you.

But what happens if there are spaces in my data values?

In the dat file it is separated to use SOH and ETX to avoid these issues...that's why I used the subfields

Re: Same field and value...but two records

Fri, 30 Jun 2017 18:21:52 GMT

Oh, actually this solution works for small amount of data, but not my large amount.

This is because for one of the fields, location, sometimes it is null.

So if you delimit on spaces, it will just skip it, and this messes up the other values.

Re: Same field and value...but two records

sunny_talwar — Fri, 30 Jun 2017 18:23:29 GMT

Makes total sense now.... Look here what I find when I export Data24 into an Excel

Have no idea what is going on here... why is one of the them with item.... would you know of anything?

Re: Same field and value...but two records

sunny_talwar — Fri, 30 Jun 2017 18:24:35 GMT

I get why you don't want to use spaces and it totally makes sense. You can keep your thread open because you still might need advice from other people....

Re: Same field and value...but two records

Fri, 30 Jun 2017 18:26:26 GMT

Is there a hidden 'item' on the end of that Boise?

Is there any way to fix this in qlikview script?

Re: Same field and value...but two records

Fri, 30 Jun 2017 18:37:12 GMT

Sunny, I have some insight into this issue.

The item appended on to the end is from the first column, @1, which is item

When I run my whole data doc in to my qvd generator with the subfields, it creates errors like this.

See the Inventory Cost Count? This is not in the data, this is a field name that I put in to count the number of rows in my data. You can view all of this in the QVW attached earlier.

This data should only have one date, the month end, but now it looks like this after running through my qvd generator.

Do you see anything wrong with my QVW?

Re: Same field and value...but two records

sunny_talwar — Sat, 01 Jul 2017 10:26:52 GMT

No, I don't... I really don't know why it is doing this

Re: Same field and value...but two records

Mon, 03 Jul 2017 13:48:42 GMT

Hmm...OK. I'll look into it, and if I can't find anything I may open another forum question.