Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Original supplier in scd type 2

I have source table with the following structure:

from

to

prodID

date

supplier1

warehouse1

1

  1. 10.10.2014

warehouse1

warehouse2

1

  1. 12.10.2014

supplier2

warehouse1

1

  1. 13.10.2014

warehouse2

shop1

1

  1. 14.10.2014

supplier3

shop4

1

  1. 15.10.2014

warehouse1

shop3

1

  1. 16.10.2014

and I want to find the last original supplier in the separate field:

from

to

prodID

date

original supplier

supplier1

warehouse1

1

  1. 10.10.2014

supplier1

warehouse1

warehouse2

1

  1. 12.10.2014

supplier1

supplier2

warehouse1

1

  1. 13.10.2014

supplier2

warehouse2

shop1

1

  1. 14.10.2014

supplier1

supplier3

shop4

1

  1. 15.10.2014

supplier3

warehouse1

shop3

1

  1. 16.10.2014

supplier2

I struggle with this task for months. The difficulty is in the  size of source table ( about 15M records for 15 month ) and that I can't build the tree using "hierarchy" cause I have to connect with the closest last date (not the same).

Any ideas would be appreciated.

2 Replies

Re: Original supplier in scd type 2

I think there's not enough information to solve this reliably. How can we tell if a value in the from column is a supplier? How can we tell what is the start and the end of a supply run?


talk is cheap, supply exceeds demand
Not applicable

Re: Original supplier in scd type 2

We can define suppliers, shops, and warehouses based on mask (warehouse id starts with '0', shop - '1', suppliers - 2..9).

We assume that supply starts from 'date' field and ends when we get the next supply.

Community Browser