Front and Back Logic Lookup

[poluvidyasagar]

Hi,

I am trying to write a script that can help achieve the following. here is the explanation of problem and desired solution.

Problem:

I have 3 tables:

First table: This tables gives information about Area, its station type and its sequence

Area_Table:

Area	StationType	StationSequence
M1	Main	1
M2	Main	2
M3	Main	3
M4	Main	4
P1	Not-Main	1
P2	Not-Main	2
P3	Not-Main	3
P4	Not-Main	4

Second Table: This tables gives information about Parts, Area where they are needed and Number of locations needed in the area

PartLocationNeeded:

Part#	Area Needed	# of Locations in the Needed Area
123	M1	1
123	M2	1
123	M4	1
345	M2	1
345	M3	1
456	M3	1
456	M4	1
567	P1	1
678	P2	1

Third Table: This table gives information where part is setup in the area and number of locations setup in that area.

PartLocationExists:

Part#	Area Where  Locations Exists	# of Locations Exists
123	M2	1
345	M3	1
456	M3	1
567	P1	1
678	P2	1

Desired Solution: table below gives desired output i am looking for.I have added comments columns for understanding purpose only

So basically, what i need is to look for each part number and where a location is needed and compare against where the location is setup. If the location is setup in needed station or needed station -1 or needed station +1 , then i am okay. other wise i should flag as NOT GOOD.

For example, Part 123 is needed in M1,M2, M4 stations but part is setup in M2 location only. So going by station sequence,M1 station requirement is satisfied because of Neededstation+1, M2 station requirement is satisfied because of location in needed station but M4 station requirement is not satisfied because no locations in needed station or needed station -1 or needed station +1.

Part#	Area Needed	# of Locations in the Needed Area	Station Sequence	Area Where  Locations Exists	# Locations Exists	Check	Comments
123	M1	1	1		0	Good	Locations in +1 Station
123	M2	1	2	M2	1	Good	Locations in Same Station
123	M4	1	4		0	Not Good	No location in -1 ,+1 station range
345	M2	1	2		0	Good	Locations in +1 Station
345	M3	1	3	M3	1	Good	Locations in Same Station
456	M3	1	3	M3	1	Good	Locations in Same Station
456	M4	1	4		0	Good	Locations in -1 Station
567	P1	1	1	P1	1	Good	Locations in Same Station
678	P2	1	2	P2	1	Good	Locations in Same Station

Can someone help me write the logic:

Let me know if you need more details.

Thanks,
Vidya

[Brett_Bleess]

Generally this is something that would require services, paid engagement.  The best place I can point you is the Design Blog area to look through the how-to posts there to see if you can find some examples of what you are trying to do there to get you going.

https://community.qlik.com/t5/Qlik-Design-Blog/bg-p/qlik-design-blog

Sorry I do not have something better for you.

Regards,
Brett

[Saravanan_Desingh]

One solution is.

Area_Table:
LOAD * INLINE [
    Area, StationType, StationSequence
    M1, Main, 1
    M2, Main, 2
    M3, Main, 3
    M4, Main, 4
    P1, Not-Main, 1
    P2, Not-Main, 2
    P3, Not-Main, 3
    P4, Not-Main, 4
];

PartLocationNeeded:
LOAD * INLINE [
    Part#, Area Needed, # of Locations in the Needed Area
    123, M1, 1
    123, M2, 1
    123, M4, 1
    345, M2, 1
    345, M3, 1
    456, M3, 1
    456, M4, 1
    567, P1, 1
    678, P2, 1
];

Left Join(PartLocationNeeded)
LOAD Area As [Area Needed], StationSequence
Resident Area_Table;

PartLocationExists:
LOAD * INLINE [
    Part#, Area Where  Locations Exists, # of Locations Exists
    123, M2, 1
    345, M3, 1
    456, M3, 1
    567, P1, 1
    678, P2, 1
];

Left Join(PartLocationExists)
LOAD Area As [Area Where  Locations Exists], StationSequence As LocSeq#
Resident Area_Table;

Drop Table Area_Table;

[Saravanan_Desingh]

Dimension: Part#, [Area Needed]

Expressions:

[# of Locations in the Needed Area]
StationSequence
[Area Where  Locations Exists]
[# of Locations Exists]
Check : If(StationSequence=LocSeq# Or StationSequence-1=LocSeq# Or StationSequence+1=LocSeq#, 'Good', 'Not Good')

[Saravanan_Desingh]

[Saravanan_Desingh]

One more update to the expression, to match with your output.

[# of Locations in the Needed Area]
StationSequence
[Area Where  Locations Exists] : If(StationSequence=LocSeq#,[Area Where  Locations Exists])
[# of Locations Exists]
Check : If(StationSequence=LocSeq# Or StationSequence-1=LocSeq# Or StationSequence+1=LocSeq#, 'Good', 'Not Good')

[Saravanan_Desingh]