topic Data modeling in QlikView

Data modeling

krish2459 — Fri, 15 Nov 2024 23:40:23 GMT

Hi,

I have been confused with the below data model request.

Date model request:

Extract MBEW sheet
Extract the History data (Historic Valuation Data with suffix H, i.e. MBEWH), include only last Reporting Month and left join with MBEW
Create new table from previous table and
1. If the Material Plant Combo has an entry in the History Table(MBEWH), then use fields from History Table(MBEWH) else use fields from Actual Table(MBEW)

Attached is the sample data.

Please provide any ideas and suggestions.

Thanks..

Re: Data modeling

cwolf — Fri, 06 Nov 2020 12:43:42 GMT

In both tables the period is used in a different meaning, in MBEWH it's a "valid to" period and in MBEW it's a "valid from" period

You need to convert both tables in 3 steps:
1. Concatenate both tables
2. Handle the different meaning of period by creating new fields ValidFrom and ValidTo
3. Make an interval match to get a period for each data set

Here is a sample script how do you can do this:

let vActPeriod=Floor(MonthStart(today())); let vMinDate=num(MakeDate(Year(Today())-2,1,1)); // Load only the last three years in datamodel let vMaxDate=Floor(MonthStart(Today())); // Step 1 Concatenate both tables t: LOAD * FROM [.\MBEWH.qvd] (qvd); Concatenate(t) LOAD *, 1 as Source FROM [.\MBEW.qvd] (qvd); // Step 2 Handle the different meaning of period tt: LOAD Hash128(MATNR,BWKEY,BWTAR) as %MBEW, *, if(Source=1,num(MakeDate(LFGJA,LFMON,1))) as ValidFromTmp, if(Source=1,$(vActPeriod),num(MakeDate(LFGJA,LFMON,1))) as ValidTo Resident t; DROP Table t; MBEW: LOAD * Where ValidTo>=$(vMinDate); LOAD *, if(IsNull(ValidFromTmp),if(Previous(%MBEW)=%MBEW,AddMonths(Previous(ValidTo),1),ValidTo),ValidFromTmp) as ValidFrom Resident tt Order by %MBEW, ValidTo; DROP Fields Source,ValidFromTmp; DROP Table tt; // Step 3 Interval match t: LOAD Distinct ValidFrom, ValidTo Resident MBEW; PERIODS: LOAD PERIOD Where Day(PERIOD)=1; LOAD RecNo()+$(vMinDate)-1 as PERIOD AutoGenerate $(vMaxDate)-$(vMinDate)+1; tt: IntervalMatch(PERIOD) LOAD ValidFrom, ValidTo Resident t; DROP Tables t,PERIODS; Inner Join (MBEW) LOAD Distinct * Resident tt; DROP Fields ValidFrom,ValidTo; DROP Table tt;

Re: Data modeling

krish2459 — Mon, 09 Nov 2020 14:39:42 GMT

Hi cwolf ,

Thanks for the reply.

Can you please expain the logic what you have post.

Seems like suggention going on other direction.

Hers is the requirement.

Extract MBEW sheet
Extract the History data (Historic Valuation Data with suffix H, i.e. MBEWH), include only Reporting Month year (Year&Month) and left join with MBEW
Create new table from previous table and
1. If the Material Plant Combo has an entry in the History Table(MBEWH), then use fields from History Table(MBEWH) else use fields from Actual Table(MBEW).

Thanks..