Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
girish_talele
Contributor

Pick last 4 values from another table

Dear Experts,

I have two tables as shown below.

First table is showing Production order Details & second is showing Billing Table.

In few Billing No, Production order is linked, but in few it is missing.

We have a requirement - If Production Order is missing in Billing Table then pick last 4 (descending order) production orders of same material & populate in the Billing Table.

Please suggest how to map the requirement. Sample qvw is attached herewith.

Capture.JPG

1 Solution

Accepted Solutions
MVP & Luminary
MVP & Luminary

Re: Pick last 4 values from another table

Maybe this:

Prd_Ord:

LOAD *, AutoNumber(PrdOrd,P_Material) as Counter

INLINE [

    PrdOrd, P_Material, P_Qty

    P1, M1, 10

    P2, M2, 20

    P3, M3, 30

    P100, M1, 1000

    P200, M2, 2000

    P300, M3, 3000

    P4, M4, 40

    P5, M4, 50

    P6, M4, 60

    P7, M4, 70

    P8, M4, 80

    P9, M4, 90

    P10, M4, 100

    P11, M5, 110

    P12, M5, 120

    P13, M5, 130

];

//===========================================================================

Join (Prd_Ord)

Load P_Material,

     Max(Counter) AS MaxCounter

Resident Prd_Ord

Group By P_Material;

//===========================================================================

MapP:

mapping

LOAD P_Material,

     if(SubStringCount(V,'|')>3, left(V,index(V,'|',4)-1),V) as V;

LOAD P_Material,

     concat(PrdOrd, '|',-Counter) as V

Resident Prd_Ord

Group by P_Material;

MapQ:

mapping LOAD PrdOrd, P_Qty

Resident Prd_Ord;

Billing:

LOAD *, Applymap('MapQ',B_PrdOrd) as B_Qty;

LOAD B_Material,

     SubField(B_PrdOrd,'|') as B_PrdOrd;

LOAD BillNo,

     B_Material,

     if(len(trim(B_PrdOrd)),B_PrdOrd,ApplyMap('MapP',B_Material)) as B_PrdOrd

INLINE [

    BillNo, B_Material, B_PrdOrd

    B1, M1, P1

    B2, M2, P2

    B3, M3, P3

    B4, M4,

    B5, M5,

] ;


talk is cheap, supply exceeds demand

View solution in original post

8 Replies
Partner
Partner

Re: Pick last 4 values from another table

Hi Girish,

Could you test the script below and come back to know if it's your expected result :

Prd_Ord:

LOAD * INLINE [

    PrdOrd, B_Material, P_Qty

    P1, M1, 10

    P2, M2, 20

    P3, M3, 30

    P4, M4, 40

    P5, M4, 50

    P6, M4, 60

    P7, M4, 70

    P8, M4, 80

    P9, M4, 90

    P10, M4, 100

    P11, M5, 110

    P12, M5, 120

    P13, M5, 130

];

Join

Billing:

LOAD * INLINE [

    BillNo, B_Material, P_PrdOrd

    B1, M1, P1

    B2, M2, P2

    B3, M3, P3

    B4, M4,

    B5, M5,

];

François

Partner
Partner

Re: Pick last 4 values from another table

I get this table:

MVP & Luminary
MVP & Luminary

Re: Pick last 4 values from another table

See attached qvw for a possible solution.


talk is cheap, supply exceeds demand
girish_talele
Contributor

Re: Pick last 4 values from another table

Thanks Gysbert Wassenaar,

But we wanted to fetch (last 4) production orders where the link was missing.

Just modify & twisted you solution a little bit & get the desired results.

I want to know is there any better way to handle this requirement? .

Regards,

Girish.

//===========================================================================

Prd_Ord:

LOAD *, AutoNumber(PrdOrd,P_Material) as Counter INLINE [

    PrdOrd, P_Material, P_Qty

    P1, M1, 10

    P2, M2, 20

    P3, M3, 30

    P100, M1, 1000

    P200, M2, 2000

    P300, M3, 3000

    P4, M4, 40

    P5, M4, 50

    P6, M4, 60

    P7, M4, 70

    P8, M4, 80

    P9, M4, 90

    P10, M4, 100

    P11, M5, 110

    P12, M5, 120

    P13, M5, 130

];

//===========================================================================

Join (Prd_Ord)

Load P_Material, Max(Counter) AS MaxCounter

Resident Prd_Ord

Group By P_Material;

//===========================================================================

Billing:

Load BillNo, B_Material INLINE [

    BillNo, B_Material, B_PrdOrd

    B1, M1, P1

    B2, M2, P2

    B3, M3, P3

    B4, M4, 0

    B5, M5, 0

] Where B_PrdOrd = 0;  // Because we want to populate only missing orders

//===========================================================================

//Join(Billing)

Left Join

Load PrdOrd AS B_PrdOrd, P_Material AS B_Material, P_Qty AS B_Qty //

Resident Prd_Ord

Where MaxCounter - Counter <= 3;

//===========================================================================

Billing1: // Load Billing table with renaming fields where Prd Ord link is exist

NoConcatenate

Load BillNo AS BillNo1, B_Material AS B_Material1, B_PrdOrd AS B_PrdOrd1 INLINE [

    BillNo, B_Material, B_PrdOrd

    B1, M1, P1

    B2, M2, P2

    B3, M3, P3

    B4, M4, 0

    B5, M5, 0

] Where B_PrdOrd <> 0;

//===========================================================================

Left Join // Left Join to get Prd ord Qty

Load PrdOrd AS B_PrdOrd1, P_Material AS B_Material1, P_Qty AS B_Qty1

Resident Prd_Ord;

//===========================================================================

Join(Billing) // Join the BIlling1 table with (main) Billing Table

Load BillNo1 AS BillNo, B_Material1 AS B_Material, B_PrdOrd1 AS B_PrdOrd, B_Qty1 AS B_Qty

Resident Billing1;

//===========================================================================

MVP & Luminary
MVP & Luminary

Re: Pick last 4 values from another table

Maybe this:

Prd_Ord:

LOAD *, AutoNumber(PrdOrd,P_Material) as Counter

INLINE [

    PrdOrd, P_Material, P_Qty

    P1, M1, 10

    P2, M2, 20

    P3, M3, 30

    P100, M1, 1000

    P200, M2, 2000

    P300, M3, 3000

    P4, M4, 40

    P5, M4, 50

    P6, M4, 60

    P7, M4, 70

    P8, M4, 80

    P9, M4, 90

    P10, M4, 100

    P11, M5, 110

    P12, M5, 120

    P13, M5, 130

];

//===========================================================================

Join (Prd_Ord)

Load P_Material,

     Max(Counter) AS MaxCounter

Resident Prd_Ord

Group By P_Material;

//===========================================================================

MapP:

mapping

LOAD P_Material,

     if(SubStringCount(V,'|')>3, left(V,index(V,'|',4)-1),V) as V;

LOAD P_Material,

     concat(PrdOrd, '|',-Counter) as V

Resident Prd_Ord

Group by P_Material;

MapQ:

mapping LOAD PrdOrd, P_Qty

Resident Prd_Ord;

Billing:

LOAD *, Applymap('MapQ',B_PrdOrd) as B_Qty;

LOAD B_Material,

     SubField(B_PrdOrd,'|') as B_PrdOrd;

LOAD BillNo,

     B_Material,

     if(len(trim(B_PrdOrd)),B_PrdOrd,ApplyMap('MapP',B_Material)) as B_PrdOrd

INLINE [

    BillNo, B_Material, B_PrdOrd

    B1, M1, P1

    B2, M2, P2

    B3, M3, P3

    B4, M4,

    B5, M5,

] ;


talk is cheap, supply exceeds demand

View solution in original post

MVP
MVP

Re: Pick last 4 values from another table

1.png

Prd_Ord:

LOAD purgechar(PrdOrd, 'P') as PrdOrdSort, * INLINE [

    PrdOrd, B_Material, P_Qty

    P1, M1, 10

    P2, M2, 20

    P3, M3, 30

    P4, M4, 40

    P5, M4, 50

    P6, M4, 60

    P7, M4, 70

    P8, M4, 80

    P9, M4, 90

    P10, M4, 100

    P11, M5, 110

    P12, M5, 120

    P13, M5, 130

];

left join (Prd_Ord)

Billing:

LOAD * INLINE [

    BillNo, B_Material, P_PrdOrd

    B1, M1, P1

    B2, M2, P2

    B3, M3, P3

    B4, M4,

    B5, M5,

];

Final_Prd_Ord:

load BillNo, B_Material, PrdOrd, P_Qty, id

where len(trim(P_PrdOrd))>0 or id <= 4;

NoConcatenate load *, if(peek(B_Material)<>B_Material, 1, peek(id)+1)  as id

resident Prd_Ord
order by B_Material, PrdOrdSort  desc;

DROP Table Prd_Ord;

Partner
Partner

Re: Pick last 4 values from another table

Hi Girish,

Can you mark a post as correct to close this issue?

Thanks,

BR,

François

girish_talele
Contributor

Re: Pick last 4 values from another table

Too Good Gysbert Wassenaar.

Thank you very much. It resolves our issue.