Solved: Re: Data Model / Script help - Qlik Community

amit_saini · ‎2016-03-16

Hi Folks ,

Below is my scenario:

MKPF:

LOAD //MANDT,

MBLNR as Doc_Num_Key,

BUDAT as %DATE_Key

FROM

$(vPath)MKPF.qvd

(qvd);

----------------------------------------------------------------------------------------------------------

LET vMinDate = num(makedate(2013));

LET vMaxDate = num(today());

Datefield:

LOAD

$(vMinDate) + IterNo() -1 as Datefield

AUTOGENERATE (1)

WHILE $(vMinDate) + IterNo() -1 <= $(vMaxDate);

Calendar:

LOAD

Datefield as %DATE_Key,

num(Datefield) as NumDate,

date(Datefield, 'DD.MMM YYYY') as Date,

year(Datefield) as Year,

month(Datefield) as Month,

'Q'&ceil(month(Datefield)/3) as Quarter,

dual(year(Datefield)&'-'&'Q'&ceil(month(Datefield)/3),year(Datefield)&ceil(month(Datefield)/3)) as Year_Quarter,

dual(year(Datefield)&'-'&month(Datefield),year(Datefield)&num(month(Datefield),'00')) as Year_Month,

dual(year(Datefield)&'-'&week(Datefield),year(Datefield)&num(week(Datefield),'00')) as Year_Week,

dual(year(Datefield)&'-'&month(Datefield)&'-'&day(Datefield),year(Datefield)&num(month(Datefield),'00')&num(day(Datefield),'00')) as Year_Month_Day,

day(Datefield) as Day,

week(Datefield) as Week,

weekday(Datefield) as Weekday,

weekyear(Datefield) as WeekYear

RESIDENT Datefield;

-------------------------------------------------------------------------------------------------

T001W:

LOAD

WERKS as Plant,

NAME1

FROM

$(vPath)T001W.qvd

(qvd);

This above data is coming from SAP and this below data I need to link from excel, which looks like below:

for each plant in 'CWC','KDT','KLA','KMX','KOC','KSI','KTX','KUS'

Data:

LOAD [Local currency],

Year

[Week of],

Plant as NAME1,

[Value of material before counting],

[Net Dollar Value of Inventory Adjustments],

[Absolute Dollar Value of Inventory Adjustments],

[Inventory Accuracy rate Net],

[Inventory Accuracy rate Absolute],

[Number of adjustments],

FG,

WIP,

[FDM's],

[Raw Material],

Components

FROM

[.............$(plant)%20Inventory%20Accuracy.xlsx ]

(ooxml, embedded labels, table is Data, filters(

Remove(Row, Pos(Top, 3)),

Remove(Row, Pos(Top, 2)),

Remove(Row, Pos(Top, 1))

));

"Year" and "[Week of]" from Data table need to be linked with Calendar + "Plant" from Data need to be linked "NAME1" from T001W table

I have simply tried renaming them as same name field , but this is not working , getting circular loop!

Please suggest me possible best way , so that I can link Year ,Week of fields from excel to SAP table + Data.Plant to T001W.NAME

Thanks,

AS

avinashelite · ‎2016-03-18

if that's the case then create a mapping table and import the data in both the tables...and don't forget to rename in both i.e both should have different name like MBEW_NAME1 etc

View solution in original post

amit_saini · ‎2016-03-16

Any Suggestion???

Thanks,

AS

MayilVahanan · ‎2016-03-16

Hi

You can able to join the T001W and Data, because T001W doens't have any KPI.

T001W+Data become one table

link between Calendar and T001W+Data is Year.

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.

avinashelite · ‎2016-03-16

In you case the excel file data will act as the linking table

try like this

1.first join or map the MKPF field to calendar table so you will have the one table

2. After the combining this table build a key with the year &'-'& week as Key

3. In the Excel you have the year and Weekof so build a key like Year &'-'& [Week of] as Key

4. finally link the T001W table using the Plant key which is already present in the excel

now this doesn't form the circular key

amit_saini · ‎2016-03-16

Plz help me with Script if possible!

Thanks,

AS

avinashelite · ‎2016-03-16

Try like this

MKPF:

LOAD //MANDT,

MBLNR as Doc_Num_Key,

BUDAT as %DATE_Key

FROM

$(vPath)MKPF.qvd

(qvd);

----------------------------------------------------------------------------------------------------------

LET vMinDate = num(makedate(2013));

LET vMaxDate = num(today());

Datefield:

LOAD

$(vMinDate) + IterNo() -1 as Datefield

AUTOGENERATE (1)

WHILE $(vMinDate) + IterNo() -1 <= $(vMaxDate);

Join (MKPF)

Calendar:

LOAD

Datefield as %DATE_Key,

num(Datefield) as NumDate,

date(Datefield, 'DD.MMM YYYY') as Date,

year(Datefield) as Year,

year(Datefield)&'-'&week(Datefield) as Key,

month(Datefield) as Month,

'Q'&ceil(month(Datefield)/3) as Quarter,

dual(year(Datefield)&'-'&'Q'&ceil(month(Datefield)/3),year(Datefield)&ceil(month(Datefield)/3)) as Year_Quarter,

dual(year(Datefield)&'-'&month(Datefield),year(Datefield)&num(month(Datefield),'00')) as Year_Month,

dual(year(Datefield)&'-'&week(Datefield),year(Datefield)&num(week(Datefield),'00')) as Year_Week,

dual(year(Datefield)&'-'&month(Datefield)&'-'&day(Datefield),year(Datefield)&num(month(Datefield),'00')&num(day(Datefield),'00')) as Year_Month_Day,

day(Datefield) as Day,

week(Datefield) as Week,

weekday(Datefield) as Weekday,

weekyear(Datefield) as WeekYear

RESIDENT Datefield;

Data:

LOAD [Local currency],

Year&'-'&[Week of] as Key,

Year,

[Week of],

Plant as NAME1,

[Value of material before counting],

[Net Dollar Value of Inventory Adjustments],

[Absolute Dollar Value of Inventory Adjustments],

[Inventory Accuracy rate Net],

[Inventory Accuracy rate Absolute],

[Number of adjustments],

FG,

WIP,

[FDM's],

[Raw Material],

Components

FROM

[.............$(plant)%20Inventory%20Accuracy.xlsx ]

(ooxml, embedded labels, table is Data, filters(

Remove(Row, Pos(Top, 3)),

Remove(Row, Pos(Top, 2)),

Remove(Row, Pos(Top, 1))

));

-------------------------------------------------------------------------------------------------

T001W:

LOAD

WERKS as Plant,

NAME1

FROM

$(vPath)T001W.qvd

(qvd);

amit_saini · ‎2016-03-17

Sorry

Still Circular loop!

Thanks,

AS

amit_saini · ‎2016-03-17

Hi,

Tried this sol too , but same issue !

Thanks,AS

Anonymous · ‎2016-03-17

Try this but i am not sure

i think you need to drop datefield after calendar creation and store Calendar into qvd.

avinashelite · ‎2016-03-17

Sorry forgot to rename the Year

MKPF:

LOAD //MANDT,

MBLNR as Doc_Num_Key,

BUDAT as %DATE_Key

FROM

$(vPath)MKPF.qvd

(qvd);

----------------------------------------------------------------------------------------------------------

LET vMinDate = num(makedate(2013));

LET vMaxDate = num(today());

Datefield:

LOAD

$(vMinDate) + IterNo() -1 as Datefield

AUTOGENERATE (1)

WHILE $(vMinDate) + IterNo() -1 <= $(vMaxDate);

Join (MKPF)

Calendar:

LOAD

Datefield as %DATE_Key,

num(Datefield) as NumDate,

date(Datefield, 'DD.MMM YYYY') as Date,

year(Datefield) as Year,

year(Datefield)&'-'&week(Datefield) as Key,

month(Datefield) as Month,

'Q'&ceil(month(Datefield)/3) as Quarter,

dual(year(Datefield)&'-'&'Q'&ceil(month(Datefield)/3),year(Datefield)&ceil(month(Datefield)/3)) as Year_Quarter,

dual(year(Datefield)&'-'&month(Datefield),year(Datefield)&num(month(Datefield),'00')) as Year_Month,

dual(year(Datefield)&'-'&week(Datefield),year(Datefield)&num(week(Datefield),'00')) as Year_Week,

dual(year(Datefield)&'-'&month(Datefield)&'-'&day(Datefield),year(Datefield)&num(month(Datefield),'00')&num(day(Datefield),'00')) as Year_Month_Day,

day(Datefield) as Day,

week(Datefield) as Week,

weekday(Datefield) as Weekday,

weekyear(Datefield) as WeekYear

RESIDENT Datefield;

drop table Datefield;

Data:

LOAD [Local currency],

Year&'-'&[Week of] as Key,

Year as Data_Year,

[Week of],

Plant as NAME1,

[Value of material before counting],

[Net Dollar Value of Inventory Adjustments],

[Absolute Dollar Value of Inventory Adjustments],

[Inventory Accuracy rate Net],

[Inventory Accuracy rate Absolute],

[Number of adjustments],

FG,

WIP,

[FDM's],

[Raw Material],

Components

FROM

[.............$(plant)%20Inventory%20Accuracy.xlsx ]

(ooxml, embedded labels, table is Data, filters(

Remove(Row, Pos(Top, 3)),

Remove(Row, Pos(Top, 2)),

Remove(Row, Pos(Top, 1))

));

-------------------------------------------------------------------------------------------------

T001W:

LOAD

WERKS as Plant,

NAME1

FROM

$(vPath)T001W.qvd

(qvd);

NOTE: do you have any other tables in the script