Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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;
What you mean by subtract previous week? Can you elaborate a little bit here?
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 |
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
Hi, thanks, I tried this but still doesnt bring the value from first qvd, any suggestion for workaroud. ?
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);
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);
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)