topic Re: Data Model / Script help in QlikView

Data Model / Script help

amit_saini — Wed, 16 Mar 2016 09:58:16 GMT

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

));

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,

Re: Data Model / Script help

amit_saini — Wed, 16 Mar 2016 11:30:22 GMT

Any Suggestion???

Thanks,

Re: Data Model / Script help

MayilVahanan — Wed, 16 Mar 2016 11:45:48 GMT

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

avinashelite — Wed, 16 Mar 2016 11:50:38 GMT

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

Re: Data Model / Script help

amit_saini — Wed, 16 Mar 2016 12:01:18 GMT

Plz help me with Script if possible!

Thanks,

Re: Data Model / Script help

avinashelite — Wed, 16 Mar 2016 12:12:48 GMT

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

Re: Data Model / Script help

amit_saini — Thu, 17 Mar 2016 10:34:25 GMT

Sorry

Still Circular loop!

Thanks,

Re: Data Model / Script help

amit_saini — Thu, 17 Mar 2016 10:35:53 GMT

Hi,

Tried this sol too , but same issue !

Thanks,AS

Re: Data Model / Script help

Anonymous — Thu, 17 Mar 2016 10:49:43 GMT

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

avinashelite — Thu, 17 Mar 2016 11:10:32 GMT

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

Re: Data Model / Script help

amit_saini — Thu, 17 Mar 2016 11:37:11 GMT

Please see the attachment.

there are 3-4 sync keys , but based on requirement those are mandatory.

But please suggest if there is some better way of doing this.

Thanks,

Re: Data Model / Script help

avinashelite — Thu, 17 Mar 2016 11:46:44 GMT

currently I don't have the QV license ...can you please share the snapshot of your data model ?? you can get this in the table viewer

Re: Data Model / Script help

amit_saini — Thu, 17 Mar 2016 11:49:11 GMT

Re: Data Model / Script help

avinashelite — Thu, 17 Mar 2016 12:12:29 GMT

For tables

ISEG: Build a custom key out of Doc_Num..&'-'&MJAHR&'-'&Material_..&'-'&ZEILE as Master_Key

and rename the keys like Doc_Num as ISEG_Doc_Num for all the four keys

similary for the table

MSEG: Build a custom key out of Doc_Num..&'-'&MJAHR&'-'&Material_..&'-'&ZEILE as Master_Key

and rename the keys like Doc_Num as MSEG_Doc_Num for all the four keys

Then build a link table as below

LINK_TABLE:

LOAD Build a custom key out of Doc_Num..&'-'&MJAHR&'-'&Material_..&'-'&ZEILE as Master_Key,

Doc_Num..&'-'&MJAHR as MKPF_Key,

Material_.. as Mater_Material_..

Resident

ISEG;

LOAD Build a custom key out of Doc_Num..&'-'&MJAHR&'-'&Material_..&'-'&ZEILE as Master_Key,

Doc_Num..&'-'&MJAHR as MKPF_Key,

Material_.. as Mater_Material_..

Resident

MSEG;

In the MKPF table

MKPF:

LOAD Doc_Num..&'-'&MJAHR as MKPF_Key,

Doc_Num.. as MKPF_Doc_Num..,

MJAHR as MKPF_MJAHR,

other fields

In the MARA table

MARA:

LOAD

Material_.. as Mater_Material_..,

MTART

Re: Data Model / Script help

amit_saini — Thu, 17 Mar 2016 12:20:57 GMT

Hi Avinash,

Thank u so much for all ur suggestions ,actually similar conept + building a fact table I have tried already , but this is not giving me correct out based on requirement , later contacted SAP specialist who helped be with linking and finally we are getting right output , even though we ate having SYC keys , now just to add excel sheet data to this datamodel , but this is everytime making circular loop!

Let me try ur suggestion one more time , I appreciate all ur help!

Thanks,

Re: Data Model / Script help

avinashelite — Thu, 17 Mar 2016 12:23:11 GMT

Please try with my suggestion it should work .....and don't forget to mark the helpful and correct answers

Re: Data Model / Script help

amit_saini — Thu, 17 Mar 2016 12:24:34 GMT

Sure

Re: Data Model / Script help

amit_saini — Thu, 17 Mar 2016 14:22:33 GMT

Hi Avinash,

I think this will work , but I'm getting 2 error's , I hope my understanding regarding script was fine.

Please see the attachment.

Thanks,

Re: Data Model / Script help

avinashelite — Thu, 17 Mar 2016 19:59:58 GMT

Replace your link code with the following code this should work

LINK_TABLE:

Load

//MBLNR&'-'&MJAHR&'-'&right([MATNR],10)&'-'&ZEILE as Master_Key,

Master_Key,

ISEG_Doc_Num_Key&'-'&MJAHR as MKPF_Key,

//MBLNR&'-'&MJAHR as MKPF_Key,----------------------(Here Getting error)

//right([MATNR],10) as Master_Material

ISEG_Material_MATNR as Master_Material

Resident

ISEG;

Load

//MBLNR&'-'&MJAHR&'-'&right([MATNR],10)&'-'&ZEILE as Master_Key,

Master_Key,

//MBLNR&'-'&MJAHR as MKPF_Key,----------------------(Here Getting error)

MSEG_Doc_Num_Key&'-'&MSEG_MJAHR as MKPF_Key,

//right([MATNR],10) as Master_Material

MSEG_Material_MATNR as Master_Material,

Resident

MSEG;

Re: Data Model / Script help

amit_saini — Fri, 18 Mar 2016 05:00:05 GMT

Now the only issue is here:

MBEW:

LOAD

right([MATNR],10) as Master_Material,

BWKEY as Plant , --------(If I comment Plant than no circular loop , else we are having problem)

BKLAS,

LBKUM

FROM

$(vPath)MBEW.qvd

(qvd);