How to solve this issue?

[#1]

I load my data like this

ajouts1:
LOAD HKUNNR as Hiérar,
     VTWEG as ncanaux
FROM
C:\Users\*******\Desktop\modele\Data_Common\KNVH.QVD
(qvd);

join (ajouts1)
ajouts2:
LOAD [VTWEG] as ncanaux,
     if([VTWEG]='20','rgpGMS',
  if([VTWEG]='23' or [VTWEG]='24','rgpIndus',
   if([VTWEG]='21','rgpRHF',
           if([VTWEG]='25','rgpAutre',
     ))))    as [New Canal distri]
FROM
C:\Users\*******\Desktop\modele\Data_Common\KNVH.QVD
(qvd);

STORE ajouts1 Into ..\..\Data_Common\transition.QVD(qvd);
DROP Table ajouts1;

LOAD Hiérar,
     ncanaux,
     [New Canal distri]
FROM
C:\Users\*******\Desktop\modele\Data_Common\transition.QVD
(qvd);

so, in my database, i have 2 fields : "canaux" and "hiérarchie".

"hiérarchie" is like "H2xxxxxx", "H4xxxxxx", "H6xxxxxx", "H8xxxxxx".

"canaux" is like "20", "21", "22", "23", "24",, "25".

I want add a new model of "canaux" like this :

"20" = 20 and rgpGMS

"23" or "24" = "23" or "24" and rpgindus

"21" = 21 and rgpRHF

"25 = 25 and rgpAutre

BUT !, an entry in canaux named "22" can be in each of the others, "canaux","22" can be rgpGMS, rgpIndus, rgpRHF or rgpAutre. This is referenced by the field "hiérarchie" because one "hiérarchie" can have lot of "canaux" but a "canaux" can only have one "hiérarchie".

How to do this, thanks for yours suggestions

thanks for your help

[chris_johnson]

Hi,

What does not work?

In your data do you have any entries where VTWEG does not equal 20,21,23,24 or 25?

In your IF statement you do not have a value for when it does not match any of the numbers so you might want to put something in there to see if it is just not matching e.g.:

if([VTWEG]='20','rgpGMS',

  if([VTWEG]='23' or [VTWEG]='24','rgpIndus',

   if([VTWEG]='21','rgpRHF',

           if([VTWEG]='25','rgpAutre', [VTWEG]

     ))))    as [New Canal distri]

That way you can see the values that are not working and might give you an idea what is going wrong

[#3]

up

[#4]

up

[#5]

Each value of HKUNNR have a possible value on Canal like 20,21,23,24,25 who are completed by a name. HKUNNR always have 20 or 21 or 23 or 24 or 25 and OFTEN 22. The field "nom" must have the same value for 20 and 22 or 21 and 22, etc...

How to do this please ?

[Miguel_Angel_Baeyens]

Can you comment if you tried Adam's solution above? A table of what you see now and what you expect would definitely help.

[#7]

There is always a value for 'ncanaux', i have voluntary don't put a value for "new canal..." on 22 because this value must take the same value as the referenced key who is the same for 20 and 22 here for exemple so when i have

rgpGMS      H2001177      20

-                  H2001177      22

i want to have

rgpGMS       H2001177     20

                                          22

when i have

rgpRHF         Hxxxxxx        25

-                     Hxxxxxx       22

i want to have

rgpRHF       Hxxxxxx        20

                                          22

[Miguel_Angel_Baeyens · Accepted solution]

You will need to find the closest corresponding value (20, 21, 23, etc.) and use its value to populate when 22 and the same hierarchy are there.

Using Peek() and Previous() will help you with that:

Previous and Peek - Script

[#9]

thank i will look at this immediatly

[#10]

i have tryed to use peek and previous in my script but this doesn't work 😕

peek is used to find a value in a table and previous to get the parent in a table, right ?