Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
Make your voice heard! Participate in the 2020 Wisdom of Crowds® Survey. BEGIN SURVEY
Highlighted
Anuhyak1
New Contributor

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
Highlighted
Ezir
Contributor II

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

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
Highlighted
Ezir
Contributor II

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

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

Highlighted
MVP & Luminary
MVP & Luminary

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

Is Dept in the same table in the script?

-Rob

Highlighted
Anuhyak1
New Contributor

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

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

Ex: g9OH-ZW5369

Highlighted
Anuhyak1
New Contributor

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

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

Highlighted
Anuhyak1
New Contributor

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

Hi Eric

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

Ex: g9OH-ZW5369

Highlighted
MVP & Luminary
MVP & Luminary

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

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

Highlighted
Anuhyak1
New Contributor

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

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