Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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);
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.
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?
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.
Hi Gysbert,
Can you please share an example (QVW file).
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
Many Thanks Gysbert!
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.