Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anuhyak1
Creator
Creator

Append characters at the end of style field based on the selection made at the department field

I have style and department  fields .  Dept is selection box which has values 512,232,789

STYLE
g9OHZW5369
q9TJTJ5258
t9NCGT5255
FPTKP0696M

If a style doesn't have a letter as the last character, add a letter based on the Dept #
                     Dept 512 = M (for M)
                     Dept 232 = P (for P)
                     Dept 789 = X (for x)

                     Example: g9OH-ZW5369 --> g9OH-ZW5369M because it comes from the M Dept

                                          FPTKP0696M --> Don't append anything it has character at the end.

               How to develop in the script

1 Solution

Accepted Solutions
Ezir
Creator II
Creator II

Hello @Anuhyak1 ,

 

Try this:

SET vDept = 512;

temp:
load * inline [
style,
g9OHZW5369,
q9TJTJ5258,
t9NCGT5255,
FPTKP0696M
];

temp2:
load style&if(isnum(right(style,1)),if($(vDept)=512,'M',if($(vDept)=232,'P',if($(vDept)=789,'X'))),'') as new_style
resident temp;

drop table temp;

View solution in original post

7 Replies
Ezir
Creator II
Creator II

Hello @Anuhyak1 ,

 

Try this:

SET vDept = 512;

temp:
load * inline [
style,
g9OHZW5369,
q9TJTJ5258,
t9NCGT5255,
FPTKP0696M
];

temp2:
load style&if(isnum(right(style,1)),if($(vDept)=512,'M',if($(vDept)=232,'P',if($(vDept)=789,'X'))),'') as new_style
resident temp;

drop table temp;
rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

Is Dept in the same table in the script?

-Rob

Anuhyak1
Creator
Creator
Author

I have to put '- ' after 4 digits from left. how to embed the code into this logic for style field

Ex: g9OH-ZW5369

Anuhyak1
Creator
Creator
Author

Yes, Dept and style are both in the same table.

Anuhyak1
Creator
Creator
Author

Hi Eric

I have to put '- ' after 4 digits from left. how to append this logic to the one you suggested

Ex: g9OH-ZW5369

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

MapDept:
Mapping Load * Inline [
Dept, Suffix
512, M
232, P
789, X
];

Data:
Load
  if(IsNum(Right(Style,1)),
    STYLE & ApplyMap('MapDept', DEPT),
    STYLE) as STYLE,
...the remainder of your load statement...

-Rob

Anuhyak1
Creator
Creator
Author

I have to put '- ' after 4 digits from left. how to append this logic to the one you suggested with Apply map.

Please help, some times H is repeating if i do logic =left(STYLE,4)&'-'& right(STYLE,len(STYLE)-4) or truncating if increase the number

Ex: g9OH-ZW5369