How to make my sales table and AR table using same SOURCE field ?

[paulyeo11]

Hi All

My below script work fine , But then i create

SOURCE field for sales table
SOURCE_AR field for AR table

My question is how to make both using same SOURCE field ?

So that in QS cloud , i just need one field for manage both table.

Paul

sales:
LOAD left(FileBaseName(), 4) AS Report_dod_1,
@50:60T as [date],
'TDS' as SOURCE,
if(@241:248T = '2',@134:147T*-1,@134:147T) as [sales]
FROM C:\Users\Paul Yeo\Dropbox\5 QV_Final\QV_RAW\Q_DOD_2018.txt (ansi, fix, no labels, header is 0, record is line);

Concatenate
LOAD
'PMC' as SOURCE,
Date( Date#([AR Invoice Date], 'DD.MM.YY'), 'DD/YY/YYY') as [date],
[Customer Name] AS COMPANY,
[Row Total (SGD)] as sales
FROM [C:\Users\Paul Yeo\OneDrive - ISDN Holdings Limited\RAW DATA SAP\SO_PMC_2019.xlsx]
(ooxml, embedded labels, table is SO_PMC_2019);

Payment:
LOAD
'TDS' as SOURCE_AR,
@1:9T as [cust_id_ar],
left(@10:38T,25) as [company_ar],
if(right(@73:90T,1)='-', '-' & left(@73:90T, len(@73:90T)-1),@73:90T) as total_ar,
Today() - @98:110T as [total number of day due]

FROM C:\Users\Paul Yeo\Dropbox\5 QV_Final\QV_RAW\AR_Aging.TXT (ansi, fix, no labels, header is 0, record is line);

Concatenate
LOAD
'PMC' as SOURCE_AR,
[Customer/Vendor Code] as [cust_id_ar],
[Customer/Vendor Name] as company_ar,
Today() - Date( Date#([AR Invoice Date], 'DD.MM.YY'), 'DD/YY/YYY') as [total number of day due],
[Outstanding w/o Tax (LC)] as total_ar
FROM [C:\Users\Paul Yeo\OneDrive - ISDN Holdings Limited\RAW DATA SAP\AR_PMC.xlsx]
(ooxml, embedded labels, table is AR_PMC);

tmp:
LOAD
min(date) AS MinDate,
max(date) AS MaxDate
RESIDENT sales;
MaxMinDate:
NOCONCATENATE LOAD
MIN(MinDate) AS MinDate,
MAX(MaxDate) AS MaxDate
RESIDENT tmp;
DROP TABLE tmp;
LET varMinDate = Num(Peek('MinDate'));
LET varMaxDate = Num(Peek('MaxDate'));
LET vToday = num(today());
DROP TABLE MaxMinDate;
//*************** Temporary Calendar ***************
TempCalendar:
LOAD
$(varMinDate) + rowno() - 1 AS Num,
date($(varMinDate) + rowno() - 1) AS TempDate,
date($(varMinDate) + rowno() - 1) AS D,
year($(varMinDate) + rowno() - 1) AS Y,
month($(varMinDate) + rowno() - 1) AS M,
date(monthstart($(varMinDate) + rowno() - 1), 'MMM-YYYY') AS MY
AUTOGENERATE $(varMaxDate) - $(varMinDate) + 1;

[paulyeo11]

Hi All

My QVW

Paul

[Arthur_Fong · Accepted solution]

In TDS_SO tab:

Try this:

sales:
LOAD left(FileBaseName(), 4) AS Report_dod_1,
@50:60T as [date],
'TDS' as SOURCE,
if(@241:248T = '2',@134:147T*-1,@134:147T) as [sales]
FROM C:\Users\Paul Yeo\Dropbox\5 QV_Final\QV_RAW\Q_DOD_2018.txt (ansi, fix, no labels, header is 0, record is line);

Concatenate
LOAD
'PMC' as SOURCE,
Date( Date#([AR Invoice Date], 'DD.MM.YY'), 'DD/YY/YYY') as [date],
[Customer Name] AS COMPANY,
[Row Total (SGD)] as sales
FROM [C:\Users\Paul Yeo\OneDrive - ISDN Holdings Limited\RAW DATA SAP\SO_PMC_2019.xlsx]
(ooxml, embedded labels, table is SO_PMC_2019);

Payment:
LOAD
'TDS' as SOURCE,
@1:9T as [cust_id_ar],
left(@10:38T,25) as [company_ar],
if(right(@73:90T,1)='-', '-' & left(@73:90T, len(@73:90T)-1),@73:90T) as total_ar,
Today() - @98:110T as [total number of day due]

FROM C:\Users\Paul Yeo\Dropbox\5 QV_Final\QV_RAW\AR_Aging.TXT (ansi, fix, no labels, header is 0, record is line);

Concatenate
LOAD
'PMC' as SOURCE,
[Customer/Vendor Code] as [cust_id_ar],
[Customer/Vendor Name] as company_ar,
Today() - Date( Date#([AR Invoice Date], 'DD.MM.YY'), 'DD/YY/YYY') as [total number of day due],
[Outstanding w/o Tax (LC)] as total_ar
FROM [C:\Users\Paul Yeo\OneDrive - ISDN Holdings Limited\RAW DATA SAP\AR_PMC.xlsx]
(ooxml, embedded labels, table is AR_PMC);

tmp:
LOAD
min(date) AS MinDate,
max(date) AS MaxDate
RESIDENT sales;
MaxMinDate:
NOCONCATENATE LOAD
MIN(MinDate) AS MinDate,
MAX(MaxDate) AS MaxDate
RESIDENT tmp;
DROP TABLE tmp;
LET varMinDate = Num(Peek('MinDate'));
LET varMaxDate = Num(Peek('MaxDate'));
LET vToday = num(today());
DROP TABLE MaxMinDate;
//*************** Temporary Calendar ***************
TempCalendar:
LOAD
$(varMinDate) + rowno() - 1 AS Num,
date($(varMinDate) + rowno() - 1) AS TempDate,
date($(varMinDate) + rowno() - 1) AS D,
year($(varMinDate) + rowno() - 1) AS Y,
month($(varMinDate) + rowno() - 1) AS M,
date(monthstart($(varMinDate) + rowno() - 1), 'MMM-YYYY') AS MY
AUTOGENERATE $(varMaxDate) - $(varMinDate) + 1;

[paulyeo11]

Hi Fong

it work fine,

Many thank 

Not sure why previouly i unable make it, now it work.

Paul