Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Now Live: Qlik Sense SaaS Simplified Authoring – Analytics Creation for Everyone: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
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
Gysbert_Wassenaar

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
Author

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.