topic Re: How to solve this issue? in QlikView

How to solve this issue?

Wed, 25 Nov 2020 16:16:04 GMT

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

Re: How to solve this issue?

chris_johnson — Wed, 07 Jun 2017 16:14:06 GMT

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

Re: How to solve this issue?

Thu, 08 Jun 2017 07:31:55 GMT

Re: How to solve this issue?

Thu, 08 Jun 2017 08:30:55 GMT

Re: How to solve this issue?

Thu, 08 Jun 2017 08:34:46 GMT

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 ?

Re: How to solve this issue?

Miguel_Angel_Baeyens — Thu, 08 Jun 2017 08:34:58 GMT

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

Re: How to solve this issue?

Thu, 08 Jun 2017 08:50:23 GMT

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

when i have

rgpRHF Hxxxxxx 25

- Hxxxxxx 22

i want to have

rgpRHF Hxxxxxx 20

Re: How to solve this issue?

Miguel_Angel_Baeyens — Thu, 08 Jun 2017 08:53:17 GMT

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

Re: How to solve this issue?

Thu, 08 Jun 2017 08:54:17 GMT

thank i will look at this immediatly

Re: How to solve this issue?

Thu, 08 Jun 2017 11:29:44 GMT

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 ?

Re: How to solve this issue?

Miguel_Angel_Baeyens — Thu, 08 Jun 2017 11:57:27 GMT

Not exactly, Previous() returns the previous value of the field, Peek() searches for a value in a field, and in both cases can be used for tables which are being loaded, not for already loaded tables (like ApplyMap() for example that requires an existing table to work). In several cases you want both, e.g.: if the previous value for Date is today, store the value in field X into field Y or

If(Previous(Date) = Today(), Peek('CurrentRate'), 0) AS MostCurrentRate

I was suggesting that because I don't know in your source or data model how this "22" value can appear or how to build a key so you can use that one to search for the corresponding value (so it would take the value from 20 or 25 respectively in your examples above).

There must be a way that you can identify to which combination of values that 22 belongs, and then set the value while you are loading, that could be the hierarchy and the other channel, or a timestamp, for example.