Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hey Guys, i need some help with this model:
I Have four tables with different information, I attached an excel file with an example.
I need to build a model that will allow me to get a
For example:
ID | BILL_ID | ID_BILL_ID | BILL_ISSUE_DATE | BILL_ISSUE_DATE_ID | BILLED_MONEY |
---|---|---|---|---|---|
10054-2 | 26 | 10054-2-26 | 01/12/2014 | 24168 | 5100 |
ID | ID_BILL_ID | BILL_ID | COLLECTION_DATE | COLLECTION_DATE_ID | COLLECTED MONEY |
---|---|---|---|---|---|
10054-2 | 10054-2-26 | 26 | 01/12/2014 | 24168 | 2500 |
10054-2 | 10054-2-26 | 26 | 01/01/2015 | 24169 | 2600 |
And take something like this:
ID | BILL_ID | ID_BILL_ID | BILL_ISSUE_DATE | BILL_ISSUE_DATE_ID | BILLED_MONEY | COLLECTION_DATE | COLLECTED MONEY | |
---|---|---|---|---|---|---|---|---|
10054-2 | 26 | 10054-2-26 | 01/12/2014 | 24168 | 5100 | 01/01/2015 | 24169 | 5100 |
-> I tried to make a MasterCalendar For each table but i think thats not the best way.
-> How can i make a good data model for take different indicators, besides those I mention.
Thanks you in advance for your help!
Well than instead of ApplyMap use Left join:
LOAD D as ID,
// ApplyMap('map_collected_money', D, ':') as [COLLECTED MONEY],
BILL_ISSUE_DATE,
BILL_ISSUE_DATE_ID,
BILLED_MONEY
FROM
[https://community.qlik.com/thread/226245]
(html, codepage is 1257, embedded labels, table is @4);
left join
LOAD ID,
sum([COLLECTED MONEY]) as [COLLECTED MONEY],
max(date(date#(COLLECTION_DATE, 'DD/MM/YYYY'), 'YYYY-MM-DD')) as COLLECTION_DATE
FROM
[https://community.qlik.com/thread/226245]
(html, codepage is 1257, embedded labels, table is @5)
group by ID;
Results:
The attachment also included.
I realy like concatenate tables, this way i can share dimensions.
FOLOW THE EXAMPLE OF THE MODEL I THINK IT'S REALY GOOD
USE SET ANALISYS FOR YOUR EXPRESSIONS. EXAMPLE : SUM({<FLAG_FATO={"POL"}>} FIELD)
Thanks you for your help!
I think this is good, but i need to take something more. How can i create this table from the 2 of the top.
D | BILL_ISSUE_DATE | BILL_ISSUE_DATE_ID | BILLED_MONEY | ||
---|---|---|---|---|---|
10054-2 | 26 | 10054-2-26 | 01/12/2014 | 24168 | 5100 |
ID | ID_BILL_ID | BILL_ID | COLLECTION_DATE | COLLECTION_DATE_ID | COLLECTED MONEY |
---|---|---|---|---|---|
10054-2 | 10054-2-26 | 26 | 01/12/2014 | 24168 | 2500 |
10054-2 | 10054-2-26 | 26 | 01/01/2015 | 24169 | 2600 |
And take something like this:
ID | BILL_ID | ID_BILL_ID | BILL_ISSUE_DATE | BILL_ISSUE_DATE_ID | BILLED_MONEY | COLLECTION_DATE | COLLECTED MONEY |
---|---|---|---|---|---|---|---|
10054-2 | 26 | 10054-2-26 | 01/12/2014 | 24168 | 5100 | 01/01/2015 | 5100 |
you can use a dinamic table
One solution might be:
map_collected_money:
mapping LOAD ID,
sum([COLLECTED MONEY])
FROM
[https://community.qlik.com/thread/226245]
(html, codepage is 1257, embedded labels, table is @5)
group by ID;
LOAD D,
ApplyMap('map_collected_money', D, ':') as [COLLECTED MONEY],
BILL_ISSUE_DATE,
BILL_ISSUE_DATE_ID,
BILLED_MONEY,
F5,
F6
FROM
[https://community.qlik.com/thread/226245]
(html, codepage is 1257, embedded labels, table is @4);
The result is:
Find the attachment.
Its a good way, if i need the last date collected i have to make a MAX(COLLECTION_DATE) in another mapping load?
Thanks!
Well than instead of ApplyMap use Left join:
LOAD D as ID,
// ApplyMap('map_collected_money', D, ':') as [COLLECTED MONEY],
BILL_ISSUE_DATE,
BILL_ISSUE_DATE_ID,
BILLED_MONEY
FROM
[https://community.qlik.com/thread/226245]
(html, codepage is 1257, embedded labels, table is @4);
left join
LOAD ID,
sum([COLLECTED MONEY]) as [COLLECTED MONEY],
max(date(date#(COLLECTION_DATE, 'DD/MM/YYYY'), 'YYYY-MM-DD')) as COLLECTION_DATE
FROM
[https://community.qlik.com/thread/226245]
(html, codepage is 1257, embedded labels, table is @5)
group by ID;
Results:
The attachment also included.
Hi Juan,
I've created some QvM - Qlikview Modules that I use to help me validate my datamodel.
Maybe you'll find them useful.
,KR Koen
PS: Don't forget to mark answers as helpful and correct.