Skip to main content
Announcements
Qlik Community Office Hours, March 20th. Former Talend Community users, ask your questions live. SIGN UP
cancel
Showing results for 
Search instead for 
Did you mean: 
bengan74
Contributor III
Contributor III

Use value from field in current post

Is there a way to get a value from a field in the current post when reloading? Like Peek(), but from the same post.

The reason is I have to use quite a long formula to get necessary information to an Full Item number field, and I have some more fields that are same data but less characters ([Complete Item No.]: 1111-111-11-11, [Item No.]: 1111-111-11 and more).

Now I use formulas like this:

LOAD

     left(variant_sku,4)&'-'&

          Num(mid(variant_sku,Index(variant_sku,'-',1)+1,Index(variant_sku,'-',2)-(Index(variant_sku,'-',1)+1)),'000')&'-'&

          mid(variant_sku,Index(variant_sku,'-',3)+1,Index(variant_sku,'-',4)-(Index(variant_sku,'-',3)+1))&'-'&

          Right(variant_sku,2)

               as [Complete Item No.],

     left(variant_sku,4)&'-'&

          Num(mid(variant_sku,Index(variant_sku,'-',1)+1,Index(variant_sku,'-',2)-(Index(variant_sku,'-',1)+1)),'000')&'-'&

          mid(variant_sku,Index(variant_sku,'-',3)+1,Index(variant_sku,'-',4)-(Index(variant_sku,'-',3)+1))

               as [Item No.]

FROM

[data.csv]

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

But I rather like a code looking something like:

LOAD

     left(variant_sku,4)&'-'&

          Num(mid(variant_sku,Index(variant_sku,'-',1)+1,Index(variant_sku,'-',2)-(Index(variant_sku,'-',1)+1)),'000')&'-'&

          mid(variant_sku,Index(variant_sku,'-',3)+1,Index(variant_sku,'-',4)-(Index(variant_sku,'-',3)+1))&'-'&

          Right(variant_sku,2)

               as [Complete Item No.],

     left([Complete Item No.],11)

FROM

[data.csv]

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

I know I can load data and then use "Resident table", but I'm curios if I can get it to work without that?

brg Johan

1 Solution

Accepted Solutions
MarcoWedel

Hi,

use Preceding LOAD:

LOAD *,

        left([Complete Item No.],11);

LOAD

    left(variant_sku,4)&'-'&

          Num(mid(variant_sku,Index(variant_sku,'-',1)+1,Index(variant_sku,'-',2)-(Index(variant_sku,'-',1)+1)),'000')&'-'&

          mid(variant_sku,Index(variant_sku,'-',3)+1,Index(variant_sku,'-',4)-(Index(variant_sku,'-',3)+1))&'-'&

          Right(variant_sku,2)

              as [Complete Item No.]

FROM

[data.csv]

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

hope this helps

regards

Marco

View solution in original post

3 Replies
MarcoWedel

Hi,

use Preceding LOAD:

LOAD *,

        left([Complete Item No.],11);

LOAD

    left(variant_sku,4)&'-'&

          Num(mid(variant_sku,Index(variant_sku,'-',1)+1,Index(variant_sku,'-',2)-(Index(variant_sku,'-',1)+1)),'000')&'-'&

          mid(variant_sku,Index(variant_sku,'-',3)+1,Index(variant_sku,'-',4)-(Index(variant_sku,'-',3)+1))&'-'&

          Right(variant_sku,2)

              as [Complete Item No.]

FROM

[data.csv]

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

hope this helps

regards

Marco

bengan74
Contributor III
Contributor III
Author

Thank you Marco, spot on!

MarcoWedel

You're welcome.

Glad it worked.

regards

Marco