Creating New fields in existing table based on specific text

[orangebloss]

I have a table called milestones which contains all milestones for all projects. I need to create a report that shows the information in the following way :

 If the milestone is not found then 'N/A' should be in the table. The script I've tried is 

Milestones:

LOAD

MilestoneProjectID    

MilestoneName,

   if(Wildmatch(upper(MilestoneName),'*MS1*'),Date(Floor(MilestoneDeadline),'DD/MM/YYYY')) as MS1,

   if(Wildmatch(upper(MilestoneName),'*MS2*'),Date(Floor(MilestoneDeadline),'DD/MM/YYYY')) as MS2,

   if(Wildmatch(upper(MilestoneName),'*MS3*'),Date(Floor(MilestoneDeadline),'DD/MM/YYYY')) as MS3,

   if(Wildmatch(upper(MilestoneName),'*MS4*'),Date(Floor(MilestoneDeadline),'DD/MM/YYYY')) as MS4,

   if(Wildmatch(upper(MilestoneName),'MS5*'),Date(Floor(MilestoneDeadline),'DD/MM/YYYY')) as MS5,

FROM [lib://Milestones.qvd]

(qvd);

But when I try and put this in a table I get results like this:

How do I get them all on the same row in the table?

I'll also need to do some calcs based on actual completion date vs the milestone deadline date - how is best to approach this?

I have a separate Projects table which would be ideal to have this information in instead of using the milestones table but without using lots of apply maps I'm not sure how to do this (currently the projects table is joined via another table so a direct link won't work as I get a circular reference).

Projects connects to Tasklists via the Project ID

Tasklists are connected to milestones via the MilestoneID

Milestones list also contains the Project ID but this is called MilestonesProjectID and is stored in the Milestones file

Any guidance appreciated!

[rwunderlich]

You will want to use the script GENERIC Load prefix for this. Here's an example

https://qlikviewcookbook.com/2010/05/use-cases-for-generic-load/

-Rob

[orangebloss]

Hi Rob,

Currently I'm left joining some of the milestone table fields to my project table, if I use generic load I get an illegal combination? 

If I were to use the generic load on my original table would that alter the structure and affect other reports / tables I already have charted?

Thanks

[marcus_sommer]

I doubt that I would tend to pivot the dates within the data-model else keeping the main-structure as it is - at least not get information like the offset between the milestones and/or to today() and/or their overall min/max and/or their count and similar stuff.

A single mapping-table which combined the project and the milestone as lookup-key would be enough and then n applymap-calls could fetch the wanted information and evaluating it against the calling record (adding them as n parallel fields would be also possible).

Further ways respectively applying them in combination with the mapping may be to concat() the milestones and/or the dates into a string and then picking the wanted information with subfield() or similat string-functions. Another possibility is to use interrecord-functions like previous() and peek() within a sorted resident-load and access the information from the other records.

To address the 'N/A' requirement for missing information I would populate the records and adding them per concatenate to the facts.

[rwunderlich]

Can you post a screenshot of your data model as it now stands?

-Rob

[Qrishna]

Some Sample data would be a good starting point for us.

[orangebloss]

Hi Rob here's the cut down view of the model, Milestones are always attached to tasklists, this may be one or more. A tasklist can only be linked to one milestone though.

[orangebloss]

Hi Marcus, could you give me an example of what you mean?

From reading your response it seems I need to 

1. Create a table with the fields in from the milestone table and the project?

2. Use apply map in the projects table to pull the information across?

Thanks

[marcus_sommer]

It might be starting with something like:

Milestones:

load *, MilestoneProjectID & '|' & MS_Type as Key, 'origin' as Source;

LOAD

MilestoneProjectID,    

MilestoneName,
Date(Floor(MilestoneDeadline),'DD/MM/YYYY')) as Date,

Wildmatch(upper(MilestoneName),'*MS1*','*MS2*','*MS3*','*MS4*','MS5*') as MS_Type,

FROM [lib://Milestones.qvd] (qvd);

MissingBase:

load distinct MilestoneProjectID from Projects; join load recno() as MS_Type autogenerate 5;

concatenate(Milestones)

load MilestoneProjectID, 0 as MS_Type, 'populated' as Source
from MissingBase where not exists(Key, MilestoneProjectID & '|' & MS_Type);

drop tables MissingBase;

m: mapping load Key, Date
resident Milestones;

NextStep:

load *, applymap('m', MilestoneProjectID & '|' & MS_Type + 1, 'default') as NextDate, ...
resident Milestones;

It's not a final solution else just elaborating the above mentioned approach in possible details ...