Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Everyone,
How to add a dynamic values to In line Table.
I have Status Table (Database Table) and Measure Table(In Line Table)
In Measure Table It is displaying following values
No of Open Items
No of Closed Items
No of Ongoing Items
No of onhold Items
No of Pending Items
but if user selects id 1 in Status Table then Status_Ids of Id 1 should merge with In line Table Values. Following Values should show when id 1 is selected
No of Open Items
No of Closed Items
No of Ongoing Items
No of onhold Items
No of Pending Items
Status 1
Status 6
Following Values should show when id 2 is selected
No of Open Items
No of Closed Items
No of Ongoing Items
No of onhold Items
No of Pending Items
Status 2
Status 7
Following Values should show when id 3 is selected
No of Open Items
No of Closed Items
No of Ongoing Items
No of onhold Items
No of Pending Items
Status 3
Status 8
and so on..
How this can be achievable. Could some one please help to resolve this issue Please find attached document for sample file and below is the code used for my app
Measure_Required:
Load * Inline [
Measure
No of Open Items
No of Closed Items
No of Ongoing Items
No of onhold Items
No of Pending Items
];
Test:
LOAD
id,
Status_Id
FROM [lib://DataFiles/In_Line.xlsx]
(ooxml, embedded labels, table is Sheet1);
Thanks,
Satya
Hi Satya ,
Try the below logic . Hope it will help .
Tab1:
LOAD * INLINE [
data
No of Open Items
No of Closed Items
No of Ongoing Items
No of onhold Items
No of Pending Items
];
join(Tab1)
Tab2:
LOAD * INLINE [
id,desc
1,status1
1,status2
2,status3
2,status4
3,status5
3,status6
];
let g=FieldValueCount('id') ;
For vlist= 1 to FieldValueCount('id')
Final:
load distinct id,data Resident Tab1 where id=$(vlist);
Concatenate(Final)
load id,desc as data Resident Tab1 where id=$(vlist);
next vlist ;
drop table Tab1 ;
exit SCRIPT ;
Thanks,
Anjee
Hi,
From the above cases, i guess your inline table has 5 defined values to merge with dynamic source (Status Table).
It is achievable by below expression:
let j=1;
Do while j< 6 // you can assign variable here based on field ID (Max value).
Load $(j) as ID,* Inline
[
STATUS_ID
CLOSED
ONGOING
ONHOLD
OPEN
PENDING
];
Let j=j+1;
loop
After this loop, concatenate with your source. Hope this helps.
Thanks.
Hi Friend,
Thank you so much for your response. Let me try this
Hi Satya ,
Try the below logic . Hope it will help .
Tab1:
LOAD * INLINE [
data
No of Open Items
No of Closed Items
No of Ongoing Items
No of onhold Items
No of Pending Items
];
join(Tab1)
Tab2:
LOAD * INLINE [
id,desc
1,status1
1,status2
2,status3
2,status4
3,status5
3,status6
];
let g=FieldValueCount('id') ;
For vlist= 1 to FieldValueCount('id')
Final:
load distinct id,data Resident Tab1 where id=$(vlist);
Concatenate(Final)
load id,desc as data Resident Tab1 where id=$(vlist);
next vlist ;
drop table Tab1 ;
exit SCRIPT ;
Thanks,
Anjee
Thanks Anjee this is what I expected