topic Urgent help: At Script level- Subtract or joints or mapping load in QlikView

Urgent help: At Script level- Subtract or joints or mapping load

Anonymous — Wed, 08 Aug 2018 17:33:30 GMT

Hi,

i'm wondering if someone can help me, I'm trying to build weekly sales from MTD numbers.

basically every Monday we download data from data warehouse which is at MTD format, I want to build something that i can start extracting data at first Monday of every month and save it as week 1 then following Monday download the data but subtract the previous week and then save it as new QVD file.

I tried left joints but failed so i though i can do it by mapping load but still can not get it work, can some one suggest something ?

he:Load FISCAL_YEAR,[In CCC] from [$(vQVDPath)\PRJ_FACT_SALES_COGS_BAL_WK_A_2018_6_30](QVD);

k:
Mapping load
FISCAL_YEAR,
[In CCC] as incccmd
Resident he;
DROP TABLE he;

h:Load *from [$(vQVDPath)\PRJ_FACT_SALES_COGS_BAL_WK_A_2018_6_31](QVD);

to:
NoConcatenate
Load
[In CCC MD] as bo,FISCAL_YEAR,

ApplyMap('k', FISCAL_YEAR,0)as incheck

Resident h;
Drop Table h;

Re: Urgent help: At Script level- Subtract or joints or mapping load

vishsaggi — Wed, 08 Aug 2018 17:56:32 GMT

What you mean by subtract previous week? Can you elaborate a little bit here?

Re: Urgent help: At Script level- Subtract or joints or mapping load

Anonymous — Wed, 08 Aug 2018 18:37:31 GMT

Hi,

so lets say we have QVW file to extract the SQL data at First monday of the month and we can generate below table and save it as QVD week1. its the MTD column that gets updated.

WK1
Year	Period	Company	product	Value MTD
2018	8	A	YY	222
2018	8	B	JJ	333
2018	8	C	k	44
2018	8	A	t	77
2018	8	B	n	-12
2018	8	C	n	-84.4
2018	8	A	n	-156.8

following monday we use the same code and generate the below table and save it as QVD MTD

MTD
Year	Period	Company	product	value MTD
2018	8	A	YY	444
2018	8	B	JJ	555
2018	8	C	k	66
2018	8	A	t	44
2018	8	B	n	22
2018	8	C	n	34
2018	8	A	n	55

i'm looking for away to be able to subtract the QVD MTD from QVD WK1 to get the wK2 result table below:

WK2
Year	Period	Company	product	Value MTD
2018	8	A	YY	222
2018	8	B	JJ	222
2018	8	C	k	22
2018	8	A	t	-33
2018	8	B	n	34
2018	8	C	n	118.4
2018	8	A	n	211.8

Re: Urgent help: At Script level- Subtract or joints or mapping load

bertinabel — Wed, 08 Aug 2018 20:49:55 GMT

Hi, Darren.

Well, if I undertand your problem is with the "Mapping"?

If so, Could you try with next suggest?

to:
NoConcatenate

Load

,ApplyMap('k', FISCAL_YEAR,0)as incheck

;
Load
[In CCC MD] as bo,FISCAL_YEAR,
Resident h;
Drop Table h;

In this case I´m using a preceded loading.

I hope I have been helpful.

Regards

Re: Urgent help: At Script level- Subtract or joints or mapping load

Anonymous — Wed, 08 Aug 2018 21:13:24 GMT

Hi, thanks, I tried this but still doesnt bring the value from first qvd, any suggestion for workaroud. ?

Re: Urgent help: At Script level- Subtract or joints or mapping load

vishsaggi — Thu, 09 Aug 2018 00:22:27 GMT

Try this?

Change the path to qvd path after FROM clause.

WK1:

LOAD Year,

Period,

Company,

product,

[Value MTD]

FROM

[https://community.qlik.com/thread/310473]

(html, utf8, embedded labels, table is @1, filters(

Remove(Row, Pos(Top, 1))

));

LEFT JOIN(WK1)

MTD:

LOAD Year,

Period,

Company,

product,

[value MTD] AS MTDValue

FROM

[https://community.qlik.com/thread/310473]

(html, utf8, embedded labels, table is @2, filters(

Remove(Row, Pos(Top, 1))

));

NoConcatenate

FinalMTD:

LOAD *, MTDValue-[Value MTD] AS MTDDiff

Resident WK1;

Drop Table WK1;

Store FinalMTD INTO filepath\FinalMTD.qvd(qvd);

Re: Urgent help: At Script level- Subtract or joints or mapping load

Anonymous — Thu, 09 Aug 2018 09:50:16 GMT

Many thanks Vishwarath, I really appreciate your help

so my final code looks like this now: but it is still failing to work. any chance you could advice if i have made a error?

WK1:

LOAD GL_ACCOUNT_CODE,LEGAL_ENTITY_CODE,COMPANY_CODE,PROFIT_CENTER_CODE,MRC,VERSION_CODE,FISCAL_YEAR,FISCAL_YEAR_PERIOD_NUMBER,Week,NTS_Factor,
[In CCC],TRANSACTIONAL_MTD_QTY,BRAVO_PRODUCT_CODE,BRAVO_PRODUCT_CODE as ORIG_BRAVO_PRODUCT_CODE,SourceType, VERSION_TYPE_CODE, %GEO_HIER_KEY,
%BRAVO_HIER_KEY,SRC_SYS_ID, [In CCC ORG]
FROM [$(vQVDPath)\PRJ_FACT_SALES_COGS_BAL_WK_A_2018_8_1*]QVD (html, utf8, embedded labels, table is @1, filters(Remove(Row, Pos(Top, 1))));

LEFT JOIN(WK1)

MTD:

LOAD GL_ACCOUNT_CODE,LEGAL_ENTITY_CODE,COMPANY_CODE,PROFIT_CENTER_CODE,MRC,VERSION_CODE,FISCAL_YEAR,FISCAL_YEAR_PERIOD_NUMBER,Week,NTS_Factor,
[In CCC],TRANSACTIONAL_MTD_QTY,BRAVO_PRODUCT_CODE,BRAVO_PRODUCT_CODE as ORIG_BRAVO_PRODUCT_CODE,SourceType, VERSION_TYPE_CODE, %GEO_HIER_KEY,
%BRAVO_HIER_KEY,SRC_SYS_ID, [In CCC ORG]AS MTDValue
FROM
[$(vQVDPath)\PRJ_FACT_SALES_COGS_BAL_WK_A_2018_8*]QVD (html, utf8, embedded labels, table is @1, filters(Remove(Row, Pos(Top, 1))));

NoConcatenate
FinalMTD:
LOAD *, MTDValue-[In CCC] AS MTDDiff
Resident WK1;

Drop Table WK1;
Store FinalMTD INTO [$(vQVDPath)\FinalMTD.qvd](qvd);

Re: Urgent help: At Script level- Subtract or joints or mapping load

vishsaggi — Thu, 09 Aug 2018 17:10:30 GMT

Can you check here

FROM [$(vQVDPath)\PRJ_FACT_SALES_COGS_BAL_WK_A_2018_8_1*]QVD

May be this you are trying to load?

FROM $(vQVDPath)\PRJ_FACT_SALES_COGS_BAL_WK_A_2018_8_1*.QVD(qvd)