Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

amit_saini
Honored Contributor III

Data Model / Script help

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

));

next

Linking should be:

"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

1 Solution

Accepted Solutions

Re: Data Model / Script help

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

26 Replies
amit_saini
Honored Contributor III

Re: Data Model / Script help

Any Suggestion???

Thanks,

AS

Re: Data Model / Script help

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.

Re: Data Model / Script help

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
Honored Contributor III

Re: Data Model / Script help

Plz help me with Script if possible!

Thanks,

AS

Re: Data Model / Script help

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
Honored Contributor III

Re: Data Model / Script help

Sorry

Still Circular loop!

Thanks,

AS

amit_saini
Honored Contributor III

Re: Data Model / Script help

Hi,

Tried this sol too , but same issue !

Thanks,AS

rgvavihs
Valued Contributor

Re: Data Model / Script help

Try this but i am not sure

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

Re: Data Model / Script help

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

Community Browser