Have a field inherit value if data in a different field matches

[#1]

Evening folks,

I am looking for some help with what I fear may be simple.

There are two fields in a data-set that I want to relate.

Every row of data has Field 1 complete - Field 1 is ProjectType

Field 2 is a number value for classification that will be filled out for only one of the rows of data.

So the data looks like this:

Table : PROJECTFILES

Project Type	Classifier
Type A
Type A
Type A
Type A
Type A	12345
Type A
Type A
Type B
Type B	67890
Type B
Type B

I want to the single classifier to inherit for each matching type - i.e. anything Type A should have the classifier of 12345

Thank you for any help you can offer

-Vince

[Oleg_Troyansky]

If you reload your data and sort it by Project Type and Classifier (Descending), you can then use functions Peek() and Previous() to populate the missing values to all rows.

cheers,

Oleg Troyansky

www.masterssummit.com - take your QlikView skills to the next level!

[#3]

Thank you for your suggestion, but I am still having some issues.

Are you saying it should be something like this?

NewField_Connection:

Load

ProjectType,

Classifier,

  IF(PREVIOUS(ProjectType)=ProjectType,

    PEEK('Classifier'),0) AS NewField

Resident PROJECTFILES

order by ProjectType,Classifier desc;

It is only populating some of the values.

[jonathandienst]

Hi

If there is always only one Classifier per ProjectType, then use:

     LOAD ProjectType,

          Concat(Classifier) As Classifier

     Resident ....

     Group By ProjectType;

HTH

Jonathan

[#5]

Hey Jonathan,

Thanks for your response.

This is sooooooo close, but for some reason - the value repeats so many times. Not just one time.

Is there anything you can think of that would make it only have one value?

Thanks,

Vince

[#6 · Accepted solution]

Hey Jonathan,

It must have had some fields with the data filled out, so the same value repeated often.

I got it to work using the following:

LOAD ProjectType,

          subfield(Concat(Classifier&'_'),'_',1) As Classifier_Concat

     Resident PROJECTFILES where Classifier<>'NULL'

     Group By ProjectType;

Thank you for your guidance

[jonathandienst]

Then all you probably need is

Concat(Distinct Classifier) As Classifier