Status

[subbareddykm]

Hi all,

By data is like below :

LOAD * INLINE [

    PR, PO, IO, Invoice, Amount

    1, 26, 6, 21, 2412

    3, 22, , 22, 2491

    , 25, 16, 23, 829

    5, 14, 25, 24, 1856

    6, , 24, 25, 1355

    7, , , 26, 2053

    8, 16, 11, 27, 1555

    9, 10, 13, 28, 2303

    , 13, 18, 29, 745

    11, , 6, 30, 2491

    12, 25, 6, 31, 1355

    13, , 10, 32, 1817

    14, 25, 19, 33, 2138

    15, 25, 10, 34, 1559

    16, , 11, 35, 1317

    17, 8, 8, 36, 1758

    , , 73, 37, 2586

    , , 83, 38, 1419

    73, , , 39, 1730

];

Now i want to create  Following Reports

1.PR  without PO

2.PR without PO  and with IO

3.PR  with PO

4.PR  with PO and IO

5.Invoices  without PR

6.Invoices  without PR- with IO

[PrashantSangle]

Hi,

In script create flag for each of it

like

LOAD *,if(len(trim(PR))=0,'N','Y') as PR_FLAG,if(len(trim(PO))=0,'N','Y') as PO_FLAG

if(len(trim(IO))=0,'N','Y') as IO_FLAG INLINE [

    PR, PO, IO, Invoice, Amount

    1, 26, 6, 21, 2412

    3, 22, , 22, 2491

    , 25, 16, 23, 829

    5, 14, 25, 24, 1856

    6, , 24, 25, 1355

    7, , , 26, 2053

    8, 16, 11, 27, 1555

    9, 10, 13, 28, 2303

    , 13, 18, 29, 745

    11, , 6, 30, 2491

    12, 25, 6, 31, 1355

    13, , 10, 32, 1817

    14, 25, 19, 33, 2138

    15, 25, 10, 34, 1559

    16, , 11, 35, 1317

    17, 8, 8, 36, 1758

    , , 73, 37, 2586

    , , 83, 38, 1419

    73, , , 39, 1730

];

Now in front end use set analysis like

1.PR  without PO

sum({<PO_FLAG={"N"}>}amount)

2.PR without PO  and with IO

sum({<PO_FLAG={"N"}>}amount)

3.PR  with PO

sum({<PR_FLAG={"Y"},PO_FLAG={"Y"}>}amount)

4.PR  with PO and IO

sum({<PR_FLAG={"Y"},PO_FLAG={"Y"},IO_FLAG={"Y"}>}amount)

5.Invoices  without PR

sum({<PR_FLAG={"N"}>}Invoices)

6.Invoices  without PR- with IO

sum({<PR_FLAG={"N"},PO_FLAG={"Y"}>}Invoices)

Regards

[subbareddykm]

Sorry For the late reply,

I want in in front end with button selection,

Bcoz i have so many table, i do the sa you , i need to use so many joins,

For every logic i need to create separate button and after clicking on it table show get open

[Anil_Babu_Samineni]

This case you should use like a vshow & vhide condition for each. But, I doubt which used is want to this? Because this kiils the performance for UI

As he suggested you can use single object(Pivot/Straight).

[MarcoWedel]

Hi,

maybe one solution might be:

tabTemp:
LOAD Dual(Flag,RecNo()-2) as Flag
Inline [
    Flag
    Y
    N
];

table1:
LOAD RecNo() as ID, * Inline [ 
    PR, PO, IO, Invoice, Amount
    1, 26, 6, 21, 2412
    3, 22, , 22, 2491
    , 25, 16, 23, 829
    5, 14, 25, 24, 1856
    6, , 24, 25, 1355
    7, , , 26, 2053
    8, 16, 11, 27, 1555
    9, 10, 13, 28, 2303
    , 13, 18, 29, 745
    11, , 6, 30, 2491
    12, 25, 6, 31, 1355
    13, , 10, 32, 1817
    14, 25, 19, 33, 2138
    15, 25, 10, 34, 1559
    16, , 11, 35, 1317
    17, 8, 8, 36, 1758
    , , 73, 37, 2586
    , , 83, 38, 1419
    73, , , 39, 1730
    72, ,81 , , 1729
    71, , , , 1728 
];

tabTemp2:
CrossTable (TempNam,TempVal)
LOAD ID, PR, PO, IO, Invoice
Resident table1;

tabFlags:
LOAD ID,
    'with '&TempNam as FlagName,
    IsNum(TempVal) as Flag
Resident tabTemp2;

DROP Tables tabTemp, tabTemp2;

tabFlag:
Generic LOAD * Resident tabFlags

hope this helps

regards

Marco