How to ammend an inline table?

[#1]

Dear Friends,

I have added an inline table in the script

LOAD

* INLINE [
Phase, stage
abc, DI2
bcd, DI3
bcd, DI3
abc, DI2
abc, DI2
]

;

I want to update the inline table at every data upload if below mentioned conditions are met, it overwrite the inline table data only if the condition are met.

=

if(Status='Completed',if(Type

='4','DI4A','DI4B'),'Inline table value')

It is possible?

Documents are enclosed.

BR

Rahul Kale

[Gysbert_Wassenaar]

Maybe something like this:

MapPhase:

mapping LOAD * INLINE [

Phase, stage

abc, DI2

bcd, DI3

];

LOAD ID,

     Status,

     Type,

     Phase,

     if(Status='Completed',if(Type='4','DI4A','DI4B'),ApplyMap('MapPhase',Phase)) as NewCode

FROM Book1.xlsx

(ooxml, embedded labels, table is Sheet1);

[#3]

Hi Gysbert

Thanks for your reply.

But now it is updating the complete table.

It is not taking the value of MapPhase when condition is not met.

[Gysbert_Wassenaar]

You're going to have to explain that. This is what you stated your condition was:

if(Status='Completed',if(Type='4','DI4A','DI4B'),'Inline table value')

Now if you apply that to your table I get this if I substitute 'Inline table value' with the applymap:

1    Completed    4    abc    DI2    DI4A

Status is completed and type is 4 -> DI4A. Correct

2    In Progress  1    bcd    DI3    DI3

Status is not completed -> lookup bcd in the map -> DI3. Correct

3    Completed    3    bcd    DI3    DI4B

Status is completed and type is not 4 -> DI4B. Correct

4    Completed    4    abc    DI2    DI4A

Status is completed and type is 4 -> DI4A. Correct

5    Completed    0    abc    DI2    DI4B

Status is completed and type is not 4 -> DI4B. Correct

So... what is incorrect and what should it be?

[#5]

Hi Gysbert

Thanks for your reply!

The second condition

2    In Progress  1    bcd    DI3    DI3

Status is not completed -> lookup bcd in the map -> DI3.

is not coming in the filed NewCode.

I want a single table having values DI3,DI4A,DI4B,DI2.

So if the condition is true then it takes value from the formula else from the inline table.

New Code

DI4A

DI3

DI4B

DI2

Currently in the new code filed it takes only formula values.

[#6]

Hi Gysbert,

Can you please share an example (QVW file).

[Gysbert_Wassenaar · Accepted solution]

You have to create the mapping table before you can use it. The script is executed consequtively so the order in which you create tables is important.

See attached qvw

[#8]

Many Thanks Gysbert!

[#9]

Hi  Gysbert,

One new challenge, in my case the phase mapping field is created within the script.

LOAD ID,

     Status,

     Type,

     [Phase 1],

     Phase2,

     [Phase 1]&'-'&Phase2 as Phase,

     if(Status='Completed',if(Type='4','DI4A','DI4B'),ApplyMap('MapPhase',[Phase 1]&'-'&Phase2)) as NewCode

FROM

(ooxml, embedded labels, table is Sheet1);

but in mapping table is does not accept the field.