topic Re: Front and Back Logic Lookup in QlikView

Front and Back Logic Lookup

poluvidyasagar — Sat, 16 Nov 2024 01:04:01 GMT

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

Re: Front and Back Logic Lookup

Brett_Bleess — Wed, 08 Apr 2020 16:38:43 GMT

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

Re: Front and Back Logic Lookup

Saravanan_Desingh — Sun, 07 Jun 2020 03:10:12 GMT

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;

Re: Front and Back Logic Lookup

Saravanan_Desingh — Sun, 07 Jun 2020 03:11:51 GMT

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')

Re: Front and Back Logic Lookup

Saravanan_Desingh — Sun, 07 Jun 2020 03:12:38 GMT

Re: Front and Back Logic Lookup

Saravanan_Desingh — Sun, 07 Jun 2020 15:37:39 GMT

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')

Re: Front and Back Logic Lookup

Saravanan_Desingh — Sun, 07 Jun 2020 15:38:11 GMT